Solved

conditional delete

Posted on 2013-06-05
8
168 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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
affinity mask in sql server 1 23
Square brackets 4 39
Managing Columnstore Indexes 2 18
Getting max record but maybe not use Group BY 2 17
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

896 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

18 Experts available now in Live!

Get 1:1 Help Now