At 05:57 AM 12/10/2004, benedicte_asselin wrote:

>I want to delete rows in a table where the primary key is not
>referenced. something like:
>DELETE FROM mytable WHERE primary_key NOT IN (SELECT theref FROM
>referertable)

That statement is going to take a long time not deleting any records
from mytable, unless referertable is empty, in which case it will
delete all of mytable.

DELETE FROM my_table m
WHERE NOT EXISTS (SELECT 1 FROM ref_table r
WHERE r.key_val = m.key_val)

will do what you want and the optimizer will (one hopes) come
up with an algorithm that reads all of my_table and for each
row performs an indexed lookup on ref_table.

>can I simply do a DELETE FROM mytable without any other test and
>only those rows that are not referenced would be deleted ?

Assuming that you have a foreign key constraint between the two
tables and that the constraint doesn't have an action like CASCADE
or SET NULL, a simple delete on my_table will simply fail if any
of the rows in my_table are referenced from ref_table. SQL rules
say that a mass update is a single verb and succeeds or fails as
a unit.

Regards,


Ann