Problem merging two rows into same primary key

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/

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;