June 14, 2006 at 3:00 am
Hi there,
hope everyone is well today?!
If I have the following query:
DELETE from table2 where description not in (select description from table1)
anything in table2 with the same description as in table1 will be deleted.
But what if i want to add for further condition. For instance; both tables have a ReferenceID.
I tried the following 2 statements but both failed:
DELETE from table2 where description, referenceID not in (select description, referenceID from table1)
and
DELETE from table2 where description AND referenceID not in (select description, referenceID from table1)
Does anyone know what the correct syntax would be for multiple columns?
Regards
Andy
June 14, 2006 at 3:10 am
I would have thought it would something like
DELETE from table2 where description NOT IN (select description from table1) AND referenceID not in (select referenceID from table1)
S
June 14, 2006 at 3:14 am
Hmmm
Unfortunately that didnt work either. 0 rows affected.
And I definitely have 2 rows in table2 that arent in table1.
June 14, 2006 at 3:34 am
Andy,
Try using the EXISTS clause instead.
like so:
delete from table2 t2
where not exists (select 1 from table1 where description=t2.description and referenceID=t2.referenceID)
Hope that helps,
June 14, 2006 at 3:39 am
Failed aswell.
Incorrect syntax near 't2'
However. If I replace the first bit:
DELETE FROM
with
SELECT * FROM
Do I get a list of the correct records im wishing to delete
June 14, 2006 at 3:45 am
Oops,
try this.
delete table2
from table2 t2
where not exists (select 1 from table1 where description=t2.description and referenceID=t2.referenceID)
June 14, 2006 at 3:52 am
yes, that it!
many thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply