Re: Problem merging two rows into same primary key

"Patrik Kudo" <kudo@pingpong.net> wrote in message
news:4292D6FA.1080300@pingpong.net...
> Hi and thanks for your reply!
>
> Martijn van Oosterhout wrote:
>>>Now to the problem. We want to merge rows with id = 2 and id = 4 into id
>>>= 1 in the asdf table with the qwert table beeing updated to reflect the
>>>change. The desired result would yeild:
>>
>>
>> Why doesn't:
>>
>> update quert set data = 1 where data = 2;
>> update quert set data = 1 where data = 4;
>> delete from asdf where id in (2,4);
>>
>> work?
> >
> > I thought update cascade only took effect when the primary key changed,
> > it updated referencing tables, not the other way round.
>
> Sure it will work, but it's quite a bit of work since there are a LOT of
> tables that need to be updated. We were hoping there was an easier way and
> before we actually took a look at how things work we were hoping it'd be
> possible to somehow take advantage of the "on update cascade" of the
> foreign keys by first droping uniqueness from primary key index. But the
> more I think about it the more impossible it seems. :(
>
> Oh, well... I guess we'll go with the massive update route.
>
> Thanks,

Patrik, use the following general stored procedure:

Input:

Master table name $master and two its primary key values  $value1 and
$value2

Output:

1. All $value2 field values in child tables are update to $value1
2. $value2 record is deleted from $master table

Algorithm:

CREATE FUNCTION merge_all(char(10), char(10) AS '

SELECT
  childtablename,
  childfieldname
FROM pg_referentialinfo
WHERE pg_referentialinfo.mastertable=$master
INTO CURSOR childs;

BEGIN TRANSACTION;
SCAN FOR ALL childs RECORDS;
  UPDATE  (childs.childtablename)  set (childs.childfieldname)=$value2
       WHERE EVAL(childs.childfieldname)=$value1;
ENDSCAN;

SELECT
  primarykeyfieldname
FROM pg_tables
WHERE pg_tables.tablename=$master
INTO CURSOR mfield;

DELETE FROM $master WHERE EVAL(mfield.primarykeyfieldname)=$value2;
COMMIT;

' LANGUAGE SQL;