SQL Server DELETE error - Subquery returned more than 1 value

Question

I having an error when trying to delete rows using a subquery:

DELETE FROM tblOrderDetails
WHERE  ProductID = (SELECT ProductID FROM tblProducts WHERE Discontinued = 1)

The error which I understand is:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

I know the subquery is returning several values, but how can I then delete those rows Thanks any help would be appreciated. Patrick

Answer 1

Try this

DELETE FROM tblOrderDetails 
WHERE ProductID IN (
    SELECT ProductID 
    FROM tblProducts 
    WHERE Discontinued = 1
)

Answer 2

Use in:

DELETE FROM tblOrderDetails WHERE ProductID IN (SELECT ProductID FROM tblProducts WHERE Discontinued = 1)