Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

conditional delete

Posted on 2013-06-05
Last Modified: 2013-06-05
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

Question by:royjayd
  • 3
  • 3
  • 2
LVL 20

Accepted Solution

dsacker earned 300 total points
ID: 39222710
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.
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 50 total points
ID: 39222713
>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..

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


Author Comment

ID: 39222833
where TA.ID NOT IN (select ID from TableB)

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

LVL 20

Expert Comment

ID: 39222840
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?
LVL 65

Expert Comment

by:Jim Horn
ID: 39222842
>why do i get a syntax error in where ?
Please copy-paste the syntax error message into this question.  Mind readers we ain't.

Author Comment

ID: 39222855
>>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.
LVL 20

Expert Comment

ID: 39222908
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.

Author Comment

ID: 39222953
ok thanks

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question