I have item table and many 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 ); CREATE TABLE orders ( anyfield CHAR(10) REFERENCES parent ON UPDATE CASCADE ); CREATE TABLE invoices ( anyotherfield CHAR(10) REFERENCES parent ON UPDATE CASCADE ); -- ... a lot of more child tables with different table and field names but -- always with same REFERENCES clause. INSERT INTO parent VALUES ('1'); INSERT INTO parent VALUES ('2'); INSERT INTO orders VALUES ('1'); INSERT INTO invoices VALUES ('1'); INSERT INTO orders VALUES ('2'); INSERT INTO invoices VALUES ('2'); BEGIN; -- Direct Postgres to update all child tables. This causes error. UPDATE parent SET code='1' WHERE code='2'; -- Remove duplicate row CREATE TABLE parent AS SELECT * FROM parent GROUP BY CODE ; COMMIT; Andrus. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings