Thread: Problem merging two rows into same primary key

Hi!

I've got a problem I can't seem to find an answer to. The problem is
simplified by this example:

1. We have two tables:

create table asdf (id serial primary key,
data text);
create table qwert (id serial,
data integer references asdf
on delete cascade on update cascade);

2. We populate both tables with the following result:

keytest=# select * from asdf;
id | data
----+------
1 | asdf
2 | asd2
3 | asd3
4 | asd4
(4 rows)

keytest=# select * from qwert;
id | data
----+------
1 | 2
2 | 4
(2 rows)


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:

keytest=# select * from asdf;
id | data
----+------
1 | asdf
3 | asd3
(2 rows)

keytest=# select * from qwert;
id | data
----+------
1 | 1
2 | 1
(2 rows)


I find no way to do this because the primary/foreign keys that would
make this easy actually makes it impossible. Are there any smart way to
do this or do I need to drop the primary key (hence also drop the
foreign keys since the drop will cascade), update the data manually and
then recreate the constraints? I hope there's an easier way beacuase in
the real scenario we're dealing with nearly 100 tables depending on that
single one with the primary key...

Thanks in advance,
Patrik Kudo

On Mon, May 23, 2005 at 04:40:12PM +0200, Patrik Kudo wrote:
> Hi!
>
> I've got a problem I can't seem to find an answer to. The problem is
> simplified by this example:
>
> 1. We have two tables:
>
> create table asdf (id serial primary key,
> data text);
> create table qwert (id serial,
> data integer references asdf
> on delete cascade on update cascade);

<snip>

> 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.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

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

"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;