Solved

conditional delete

Posted on 2013-06-05
8
167 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach 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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now