Search Postgresql Archives

Re: Deleting conflicting rows when creating a foreign key

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Craig Ringer wrote:
Richard Huxton wrote:

DELETE FROM table1 WHERE col1 NOT IN (SELECT id from table2);

Just as a side note: If you have a large number of missing IDs and don't
want to wait a long time, you may be better off with something like
(untested, but I think it's right - TEST FIRST):

SELECT *
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.ref_col=t2.id)
WHERE t2.id IS NULL;

-- check that the rows to be deleted are OK

DELETE FROM table1
USING table1 t1 LEFT OUTER JOIN table2 t2 ON (t1.ref_col=t2.id)
WHERE table1.id = t1.id AND t2.id IS NULL;

(by the way, being able to specify an explicit join method in a DELETE
... USING or update ... USING would be *great*).

--
Craig Ringer
Thanks for the advice, Craig, I didn't know about that syntax before.

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux