"Patrik Kudo" <kudo@xxxxxxxxxxxx> wrote in message news:4292D6FA.1080300@xxxxxxxxxxxxxxx > 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; ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
