I have item table and a lot of child tables where the items are used. I want to merge two item codes into single item in all tables. It is not nice to write a lot of separate UPDATE statements for each table. So I want to utilize REFERENCES clause for merging.
I tried the following code but got duplicate key error in UPDATE statement.
Any idea how to impement this?
CREATE TABLE parent ( code CHAR(10) PRIMARY KEY );
BEGIN; -- Direct Postgres to update all child tables. This causes error. UPDATE parent SET code='1' WHERE code='2'; -- Remove duplicate row
That's the problem - you can't have a duplicate row at *any* time with a primary key. The UNIQUE constraint is instant and can't be deferred (at least, not yet).
However, in this case I would simply write a function:
CREATE FUNCTION merge_all(char(10), char(10) AS ' UPDATE table_1 SET col_1=$2 WHERE col1=$1; UPDATE table_2 SET col_2=$2 WHERE col2=$2; ...etc... ' LANGUAGE SQL;
Then: SELECT merge_all('OLD_VAL','NEW_VAL') for each value (you could even join to your "parent" table if all the values are in there). All the updates in the function take place in the same transaction, so if there are any problems then all changes will be rolled back.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
