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?
You can do this:
delete from mytable
where (id, name) in ((1, 'xyz'),
(2, 'abc'),
(3, 'abc'));
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).
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);
Id = 1 and Name = 'abc' would meet your criteria, but not his.