[SQL] DELETE Query with INNER JOIN?

Question

0.00/5 (No votes)

See more:

I'm currently have this query:

SQL

IF NOT EXISTS(SELECT * FROM Table1 WHERE Value = 1)
BEGIN
DELETE FROM Table2 WHERE Value = 1
END

Is it possible to use INNER JOIN, to delete all the rows from Table2 if a value doesn't exist in any of the other table's rows. Because using this would require extracting all the Values from Table2 first, then manipulating it, etc...

The query should be: DELETE all the values from Table2 which do not appear in Table1.
Instead of, DELETE value if it doesn't exist every time...

Posted 30-Oct-11 0:06am

ShacharK

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sander Rossel · Answer 1 · 2011-10-30T00:40:00

Yeah, you can simply delete rows from a table using an INNER JOIN.

SQL

DELETE t2 FROM table2 AS t2
INNER JOIN table1 ON table1.Value = t2.Value
WHERE table1.Value = 1

MSDN mentions joins in delete statements: Delete Statement[^]
Here is a full example: Delete with INNER JOIN[^]
Hope that helps :)

Amir Mahfoozi · Answer 2 · 2011-10-30T00:40:00

Solution 1

SQL

delete table2 where value not in 
(select value from table1)

Hope it helps

Posted 30-Oct-11 0:40am

Amir Mahfoozi

Abhinav S · Answer 3 · 2011-10-30T00:40:00

Solution 2

Try

SQL

DELETE FROM table1
WHERE NOT EXISTS
  ( select value
     from table2 where value = 1 )

Warning: I have never run a similar query myself.

Posted 30-Oct-11 0:40am

Abhinav S

Comments

Amir Mahfoozi 30-Oct-11 6:42am

DELETE all the values from Table2 which do not appear in Table1