Solved

conditional delete

Posted on 2013-06-05
8
171 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Help 27 67
MS SQL + Insert Into Table - If Doesnt Exist 9 56
SQL works but want to get the XML node data separately 11 25
SQL- GROUP BY 4 21
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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…

679 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