On 04/26/2014 08:16 PM, Leyne, Sean wrote: > >> I regularly find myself having to merge two (or more) records referenced by >> numerous foreign keys... resulting in having to update many tables to >> perform this merge. I would find it really useful if there was (and maybe >> there already is and I just didn't found it) a way to do this in one update >> (performing the cascade). >> >> If I am not the only one who would consider this useful, I will gladly add a >> ticket for this... I just didn't want to "pollute" the tracker with >> something I >> would be the only one finding it useful. >> >> Example : Table Patient with a sequence ID often has duplicate (after two >> clinics merge or simply data entry error) but has numerous dependant tables >> referencing this ID. > I don't think that this is a feature that should be considered. > > There are existing ways to resolve this: > > 1- Rethink your schema, you could treat the "Patient" table as an "Clinic > Patient/Alias" table and define a separate "Master Patient" to which each > "Clinic Patient/Alias" entry would refer to. In this way you only have 1 > level of entries to update. > > 2- create SPs which perform the required updates, based on coded rules for > each column/FK. Yes, this requires that you create/maintain the SPs but you > are in control of the operation/scope. > > 3- create an SP which uses system tables to navigate the schema to follow the > FK relationships, and performs the necessary updates. The SP would need the > name of the master table to start from, the name of the column to be updated, > the original key value and the new key value.
Sean, certainly it's possible to replace almost any of higher than plain select/insert/update/delete SQL operators with some SP (including MERGE itself). Certainly as a replacement of missing operators it's a solution. But somewhy people like to have things like group by, window functions, merge, etc. built-in. A case described here seems to realistic and widely used in development. I.e. I would like to say yes, it's good suggestion. ------------------------------------------------------------------------------ "Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE Instantly run your Selenium tests across 300+ browser/OS combos. Get unparalleled scalability from the best Selenium testing platform available. Simple to use. Nothing to install. Get started now for free." http://p.sf.net/sfu/SauceLabs Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
