CODEWITHSUNDEEP
sqlsql-serversql-delete
user2729861
user2729861

Reputation: 5

Writing the SQL query based on 2 tables

I am really confused writing this SQL query, it might be easy but I still cannot come to the right solution.

Idea: Delete rows (foreign keys) from TableA based on TableB, if in TableB exist Primary Keys which match some other value within TableB.

For table B it should look like this:

SELECT Column1 
FROM TableB
WHERE Column2 = 'Value';

And then

Delete rows in TableA which match to values inside of Column1 (TableB).

Upvotes: 0

Views: 39

Answers (2)

victor175
victor175

Reputation: 624

Assuming that you need to match ColumnX in TableA:

DELETE FROM TableA 
WHERE ColumnX IN (SELECT Column1 
                  FROM TableB 
                  WHERE Column2 = 'Value');

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

IN operator is good when you have hard coded values in IN operator like where SomeCoumn IN ('value1', 'Value2')

Or you are checking against a Primary key column like WHERE SomeColumn IN (select PK_Column from SomeTable)

Because in either of the above cases you will not have a NULL value inside your IN operator.

Null values inside IN operator brings back unexpected results.

A better option would be to use Exists operator... something like....

DELETE FROM TableA 
WHERE EXISTS ( SELECT 1 
               FROM TableB 
               WHERE TableA.ColumnX = TableB.Column1
                AND TableB.Column2 = 'Value'
            );

Upvotes: 1

Related Questions