How can I delete multiple values in SQL?
I know how to delete a rowset using statements like this:
DELETE FROM my_table WHERE id=23 AND year=2012 AND value=16
DELETE FROM my_table WHERE id=17 AND year=2012 AND value=16
DELETE FROM my_table WHERE id=64 AND year=2012 AND value=16
But I would like to combine the above three statements into one DELETE where id is 23, 17 or 64.
What is the syntax for this in Oracle SQL?
+3
WilliamKF
source
to share
4 answers
You can use the SQL keyword IN
. For example:
DELETE FROM my_table WHERE id IN (23, 17, 64) AND year=2012 AND value=16
Note. Oracle has a limit of 1000 items per list.
+8
Mike christensen
source
to share
DELETE FROM my_table
WHERE id in (17, 23, 64) AND year=2012 AND value=16
+5
Michael Fredrickson
source
to share
You can link conditions AND/OR
to achieve the same effect. In this case, a simple one IN
will solve your goal.
DELETE FROM my_table WHERE id IN (23, 17, 64) AND year=2012 AND value=16
+3
Achrome
source
to share
What about:
DELETE FROM my_table WHERE (id=23 or id=17 or id=64) AND year=2012 AND value=16
+1
Daniel Kelley
source
to share