SQL - Delete and subquery

Question

The following is a sql query which I wrote to delete all records in the placement table where the placement.op_number = a list of returned records.

The subquery works fine alone and returns only the OP_NUMBER column (with two rows).

The error I get is:

At most one record can be returned from the Sub-Query

DELETE
FROM PLACEMENT
WHERE PLACEMENT.OP_NUMBER = (SELECT OP_NUMBER
                             FROM Opening
                             WHERE opening.qual_code = "SEC-45");

What am I doing wrong?

Answer 1

Use IN instead of equals:

delete
from PLACEMENT
where PLACEMENT.OP_NUMBER in (
        select OP_NUMBER
        from Opening
        where opening.qual_code = "SEC-45"
        );

Answer 2

DELETE
FROM PLACEMENT
WHERE EXISTS (SELECT OP_NUMBER FROM Opening
               WHERE opening.qual_code = "SEC-45" 
              AND OP_NUMBER = PLACEMENT.OP_NUMBER);

Answer 3

Currently you are comparing PLACEMENT.OP_NUMBER to a recordset - an Int vs a recordset. You will want to use the IN keyword in order to compare a condition with multiple values

Your new query should look like this -

DELETE
FROM PLACEMENT
WHERE PLACEMENT.OP_NUMBER IN (SELECT OP_NUMBER
                             FROM Opening
                             WHERE opening.qual_code = "SEC-45");