PostgreSQL Discussion - Problem merging two rows into same primary key

Author

Problem merging two rows into same primary key

Patrik Kudo

2005-05-23, 11:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Martijn van Oosterhout

2005-05-23, 11:23 am

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

Patrik Kudo

2005-05-24, 3:23 am

Hi and thanks for your reply!

Martijn van Oosterhout wrote:
>
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Andrus

2005-05-24, 1:23 pm

"Patrik Kudo" <kudo@pingpong.net> wrote in message
news:4292D6FA.1080300@pingpong.net...
> Hi and thanks for your reply!
>
> Martijn van Oosterhout wrote:
>
> 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)=$val
ue2
WHERE EVAL(childs. childfieldname)=$val
ue1;
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;

Sponsored Links