Re: Cascade Update Trigger Problems

[Quoted] [Quoted] CREATE TRIGGER companyupdate ON company FOR UPDATE BEGIN
   UPDATE company
   SET companykey=1
   WHERE companykey in (2, 3, 4);
END; I know updating keys is not the most correct way to do it, but if anyone has a better way to merge these I'm all ears.

The decision regarding what companies get merged has already been made, & I have a set of keys for each set of "from" & "to" companies. In this case, I want to merge all of these companies into one. In the db they register as 4 companies, but I'm trying to clean it up so that only GE exists. But I also want all the orders/employees/other child records to be updated as well, so it's not as easy as just deleting the duplicates. Unfortunately I'm stuck with the existing database & I can't change its structure, nor can I turn back time & prevent the duplicates from getting into the database, so I have to come up with a pound of cure instead of an ounce of prevention.

Frank van Bortel wrote:
> dleenhouts_at_gmail.com schreef:
> > Does anyone know how to use a trigger to update a table (& all child
> > tables) so that an existing pk is updated to another existing pk. For
> > instance, merging records so that:
> >
> > Key CompanyName
> > 1 GE
> > 2 General Electric
> > 3 general electric
> > 4 G.E.
> >
> > All become one single record. I would even be satisfied by assigning
> > all 4 of these into a 5th record as long as all related records are
> > attached. These also have a number of child records associated with
> > them as well (orders, employees, etc).
> >
> > Thanks
> >
>
> What have you engineered so far?
>
> What bothers me is "an existing pk is updated ..." Primary Keys
> are not meant to be updated (let the discussions begin!), not in
> 99.9% of all systems, anyway.
>
> And based on WHAT should the records be merged? GE is not the
> same as general electric, you know.
> --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...
Received on Mon Aug 07 2006 - 21:21:29 CEST