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 );
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.