Compare fields in different tables

I need to compare filed A in Table A to Field B in Table B. I then need to
delete any records in Table A that don't exist in Table B. I ran the
following Minus query to show the different records, just waht to make sure
I have the correct syntax to delete these recors.
select ID from tableB
MINUS
select ID from tableA
Thanks.
Ben
Hi,
Keyword MINUS can be used in Oracle. AFAIK, MINUS wil not serve the
comparison between 2 tables in SQL Server.
You need to use either NOT EXISTS or NOT IN in SQL Server for comparison.
SELECT t1.Col1 FROM Table1 AS t1
WHERE NOT EXISTS( SELECT * FROM Table2 AS t2
WHERE t2.Col1 = t1.Col1 )
or:
SELECT t1.Col1 FROM Table1 AS t1
WHERE t1.Col1 NOT IN( SELECT t2.Col1 FROM Table2 AS t2 )
Thanks
Hari
SQL Server MVP
"Ben" <benNOSPAMhough@.comcast.net> wrote in message
news:uMp89G6lFHA.1412@.TK2MSFTNGP09.phx.gbl...
>I need to compare filed A in Table A to Field B in Table B. I then need to
>delete any records in Table A that don't exist in Table B. I ran the
>following Minus query to show the different records, just waht to make sure
>I have the correct syntax to delete these recors.
> select ID from tableB
> MINUS
> select ID from tableA
> Thanks.
> Ben
>
|||SQL Server doesn't support a MINUS operator.
Try the following (untested):
DELETE FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TableB
WHERE TableB.b = TableA.a)
David Portas
SQL Server MVP