Zerjack Zerjack - 6 months ago 12
SQL Question

Deleting multiple rows on the same query

Say I have a table making a relationship between

CUSTOMER_ID
, and
B_CODE
.
There are multiple instances of
CUSTOMER_ID
having different
B_CODE
. I'm making an UI capable of adding and removing codes from this table based on a swaplist UI. I know how to insert multiple rows in the same query, but I'm not sure of how to do the same when removing.

I know I could just remove all instances of said
CUSTOMER_ID
and then adding what is left on the list. But that seems like a bad way to do it.

So how can I remove multiple rows on that table with the same ID, different codes, in the same query, but without removing all the instances of such ID?

So for example I got this

+------------+---------+
| 123 | A |
| 124 | B |
| 123 | C |
| 123 | D |
| 124 | E |
| 123 | F |
+------------+---------+


And I only want to delete
123 -> A
,
123 -> C
, and
123 -> F
but not
123 -> D
, and in the same query.

Answer

SqlFiddle

(a) If you want to delete all rows that you know the b_code for:

delete from t where customer_id = 123 and b_code in ('A','C','F');

(b) Or if you want to delete all rows except the one you know the b_code for

delete from t where customer_id = 123 and b_code not in ('D');

Use (a) if you have few b_codes selected for deletion.

Use (b) if you have few b_codes to keep.