'Delete a record from a table referring to 2 or more columns from another table

Consider having 2 tables as follows.

Table 1:

Unit SKU number Active
A 1 Y
B 2 Y
c 3 Y

Table 2:

Unit SKU number description
X 4 Apple
B 2 Mango
Y 5 Grapes
z 6 Banana

I wanted to delete record B,2,Y from table 1 by referring to table 2 where values in columns Unit and SKU number match.

I tried using the following query but it didn't seem to work

DELETE FROM table1 
WHERE (Unit, SKU_Number) IN (SELECT Unit, SKU_Number FROM table2);

The error was

An expression of non-boolean type specified in a context where a condition is expected, near ','

Can someone please help me understand what I am doing wrong here or help me rewrite the SQL query to achieve the required objective?

sqlsql-server


Solution 1:[1]

You could use similar logic with exists:

DELETE
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2
              WHERE t2.Unit = t1.Unit AND t2.SKU_Number = t1.SKU_Number);

Solution 2:[2]

You can try using this query, assuming Unit of Table 1 is unique:

DELETE FROM table1
WHERE table1.Unit IN (
  SELECT table1.Unit 
  FROM table1
  LEFT JOIN table2 ON table1.Unit = table2.Unit
                AND table1.SKU_Number = table2.SKU_Number
)

If unit is not an unique field, simply replace it with whichever field is unique, or with primary key of Table 1.

Solution 3:[3]

You can use inner join for delete:

DELETE t1
FROM table1  t1
INNER JOIN table2 t2
  ON t1.unit=t2.unit and t1.SKU_Number = t2.SKU.Number

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 Nguy?n Minh Hi?u
Solution 3 Dale K

Related Questions