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?

score 5 · Accepted Answer · answered May 17 '12 at 19:30

5

Use IN instead of equals:

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

answered May 17 '12 at 19:30

D'Arcy Rittich

167,292
40
290
283

I think `IN` can fail if there are null OP_NUMBER records in opening table – rs. May 17 '12 at 19:32
Thanks Mate - Will Accept As Soon as i am able to ! – Markus May 17 '12 at 19:33

score 1 · Answer 2 · answered May 17 '12 at 19:30

1

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

answered May 17 '12 at 19:30

rs.

26,707
12
68
90

score 1 · Answer 3 · answered May 17 '12 at 19:33

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

SQL - Delete and subquery

3 Answers3