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

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?

CodePudding user response：

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);

CodePudding user response：

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.

CodePudding user response：

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