CODEWITHSUNDEEP
oracle-database
PeaceIsPearl
PeaceIsPearl

Reputation: 339

Deleting multiple rows in oracle

DELETE FROM MYTABLE WHERE ID = 1 and NAME ='xyz';
DELETE FROM MYTABLE WHERE ID = 2 and NAME ='abc';
DELETE FROM MYTABLE WHERE ID = 3 and NAME ='abc';

I have multiple delete statements mentioned above. How can I delete them in less statements. Will I have to write 100 delete statements?

Upvotes: 5

Views: 14813

Answers (3)

Mangesh
Mangesh

Reputation: 1

DELETE FROM MYTABLE 
  WHERE ID IN (1, 2, 3) AND NAME IN ('XYZ', 'ABC');

If your id field is unique then use:

DELETE FROM MYTABLE WHERE ID IN (1, 2, 3);

Upvotes: -2

Lukasz Szozda
Lukasz Szozda

Reputation: 176164

You could use IN:

DELETE FROM MYTABLE
WHERE (ID, NAME) IN (SELECT 1 AS ID, 'xyz' AS NAME FROM dual UNION ALL
                     SELECT 2 AS ID, 'abc' AS NAME FROM dual UNION ALL
                     SELECT 3 AS ID, 'abc' AS NAME FROM dual);

Of course inside subquery you could use any select (for instance from global temporary table).

Upvotes: 1

Tony Andrews
Tony Andrews

Reputation: 132710

You can do this:

delete from mytable
where (id, name) in ((1, 'xyz'),
                     (2, 'abc'),
                     (3, 'abc'));

Upvotes: 14

Related Questions