Click here to Skip to main content
15,032,033 members
Please Sign up or sign in to vote.
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

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 :)
   
SQL
delete table2 where value not in 
(select value from table1)


Hope it helps
   
Try
SQL
DELETE FROM table1
WHERE NOT EXISTS
  ( select value
     from table2 where value = 1 )


Warning: I have never run a similar query myself.
   
Comments
Amir Mahfoozi 30-Oct-11 6:42am
   
DELETE all the values from Table2 which do not appear in Table1

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900