SQL Server DELETE error - Subquery returned more than 1 value

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 return开发者_StackOverflowing several values, but how can I then delete those rows Thanks any help would be appreciated. Patrick

Try this

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

Use in:

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

Try this:

 DELETE OD
 FROM tblOrderDetails AS OD
 INNER JOIN tblProducts as P ON P.ProductID =OD.ProductID 
 WHERE P.Discontinued = 1; 

IN will allow you to define a set the should be deleted and not a single row.

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

DELETE 
  FROM tblOrderDetails
 WHERE EXISTS (
               SELECT * 
                 FROM tblProducts AS T2
                WHERE T2.ProductID = tblOrderDetails.ProductID
                      AND T2.Discontinued = 1
              );

But what do the auditors think about the loss of historical data for now discontinued products...?