conditional delete

hi guys

I have a requirment where i need to delete data from TAbleA depending on a condition in TableB
The condition is
TableB.Id should not exist in TableA

TableA and TableB have column 'Id'


I am trying to write this sql but having problems

delete top (50) from TAbleA TA where TableB.id NOT IN (select id from TableA)    

any help wil be appreciated

thanks
royjaydAsked:
Who is Participating?
 
dsackerConnect With a Mentor Contract ERP Admin/ConsultantCommented:
You can delete from TableA where id not in (select id from TableB), and you can delete from TableB where id not in (select id from TableA).

However, to delete rows from TableA where the TableB.id is not in (select id from TableA) does not tell you which id's you want to delete in TableA.

This criteria is inapplicable.
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>TableB.Id should not exist in TableA
Does this mean ... TableB.ID should not exist in TableA.ID, or in TableA.something else?

This would also work..

DELETE TableA
FROM TableA a
   INNER JOIN TableB b on b.Bid = a.Bid


http://stackoverflow.com/questions/439750/t-sql-selecting-rows-to-delete-via-joins
0
 
royjaydAuthor Commented:
DELETE TOP (50000) FROM TableA TA
where TA.ID NOT IN (select ID from TableB)

why do i get a syntax error in where ?
Is this not correct?

thanks
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
dsackerContract ERP Admin/ConsultantCommented:
Take off the TA and TA., just use where ID NOT IN (select ID from TableB).

DELETE TOP (50000) FROM TableA
WHERE ID NOT IN (select ID from TableB)

What do you get?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>why do i get a syntax error in where ?
Please copy-paste the syntax error message into this question.  Mind readers we ain't.
0
 
royjaydAuthor Commented:
>>Take off the TA and TA., just use where ID NOT IN (select ID from TableB).
well if i take off the TA it works but why do i have to take off, i want it for clear understanding incase the sql grows.

>>Please copy-paste the syntax error message
error is incorrect syntax near where.
0
 
dsackerContract ERP Admin/ConsultantCommented:
You can't alias the table on the UPDATE or DELETE line. You can join it later to its same name, where you can then alias it.
0
 
royjaydAuthor Commented:
ok thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.