Solved

conditional delete

Posted on 2013-06-05
8
169 Views
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

thanks
0
Comment
Question by:royjayd
  • 3
  • 3
  • 2
8 Comments
 
LVL 20

Accepted Solution

by:
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.
0
 
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..

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
 

Author Comment

by:royjayd
ID: 39222833
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
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

by:dsacker
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?
0
 
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.
0
 

Author Comment

by:royjayd
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.
0
 
LVL 20

Expert Comment

by:dsacker
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.
0
 

Author Comment

by:royjayd
ID: 39222953
ok thanks
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL profiler equivalent in MS-Access 3 58
Generate Weekly Schedule 15 28
sql query questions 2 34
MS SQL Delete Query 9 30
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, …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

808 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