If you do this:
INSERT INTO parent VALUES ('1');
INSERT INTO parent VALUES ('2');
UPDATE parent SET code='1' WHERE code='2';then the UPDATE will clearly fail because you are trying to create an additional record with a value of 1 when there already exists a row with a value of 1 in the column that has been established as a primary key.
I've only been explaining general database theory and the rules of SQL in response to your posts because I'm still having a difficult time understanding what you're trying to accomplish.
-tfo
-- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC
Strategic Open Source — Open Your i™
http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005
On Mar 25, 2005, at 1:23 PM, Andrus Moor wrote:
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 6: Have you searched our list archives?
http://archives.postgresql.org
