CODEWITHSUNDEEP
sqloracle-database
WilliamKF
WilliamKF

Reputation: 43089

How to delete several values in SQL?

I know how to delete a set of rows with a statements like these:

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 a single DELETE where id is either 23, 17, or 64.

What is the syntax for doing this in Oracle SQL?

Upvotes: 4

Views: 4920

Answers (4)

Mike Christensen
Mike Christensen

Reputation: 91580

You can use the SQL IN keyword. For example:

DELETE FROM my_table WHERE id IN (23, 17, 64) AND year=2012 AND value=16

Note: Oracle has a limit of 1,000 items in a list.

Upvotes: 10

Achrome
Achrome

Reputation: 7821

You can chain the AND/OR conditions to achieve the same effect. In this case, a simple IN would solve your purpose.

DELETE FROM my_table WHERE id IN (23, 17, 64) AND year=2012 AND value=16

Upvotes: 3

Daniel Kelley
Daniel Kelley

Reputation: 7737

How about:

DELETE FROM my_table WHERE (id=23 or id=17 or id=64) AND year=2012 AND value=16

Upvotes: 1

Michael Fredrickson
Michael Fredrickson

Reputation: 37378

DELETE FROM my_table 
WHERE id in (17, 23, 64) AND year=2012 AND value=16

Upvotes: 5

Related Questions