delete record does not exist in table

I want to delete records in table1 if account number does not exist in table
2.
I have following code:
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
Should the above code work?
Any informaion is great appreciated,Yes, that should work just fine...
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in
(SELECT ACCOUNT_NUMBER FROM TABLE2)
You can also use Not Exists
DELETE TABLE1 T1
WHERE Not Exists
(SELECT * FROM TABLE2
Where ACCOUNT_NUMBER =
T1.ACCOUNT_NUMBER)
"Souris" wrote:

> I want to delete records in table1 if account number does not exist in tab
le2.
> I have following code:
> DELETE FROM TABLE1
> WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
> Should the above code work?
> Any informaion is great appreciated,
>|||To add to CBretana's response, your NOT IN construct will work as expected
unless there is a NULL ACCOUNT_NUMBER in TABLE2. No rows will be returned
when there are one or more NULL values. Personally, I prefer NOT EXISTS
because that produces the behavior desired in most situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:DF0FDD5A-FC29-4120-8D09-D070F5E43520@.microsoft.com...
>I want to delete records in table1 if account number does not exist in
>table2.
> I have following code:
> DELETE FROM TABLE1
> WHERE ACCOUNT_NUMBER not in(SELECT ACCOUNT_NUMBER FROM TABLE2)
> Should the above code work?
> Any informaion is great appreciated,
>|||Dan
I agree , but if you change 'a little bit :-)' his query it should work as
well as NOT EXISTS .
People just forget with NOT IN to add WHERE condition with an outer table.
DELETE FROM TABLE1
WHERE ACCOUNT_NUMBER not in
(SELECT ACCOUNT_NUMBER FROM TABLE2 Where TABLE1.ACCOUNT_NUMBER =
TABLE2 .ACCOUNT_NUMBER)
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OlRMglaRFHA.252@.TK2MSFTNGP12.phx.gbl...
> To add to CBretana's response, your NOT IN construct will work as expected
> unless there is a NULL ACCOUNT_NUMBER in TABLE2. No rows will be returned
> when there are one or more NULL values. Personally, I prefer NOT EXISTS
> because that produces the behavior desired in most situations.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Souris" <Souris@.discussions.microsoft.com> wrote in message
> news:DF0FDD5A-FC29-4120-8D09-D070F5E43520@.microsoft.com...
>