Reputation: 916
SQL Select and Delete help
How do you go about selecting or deleting a specific list of numbers from a table?
For example,
select * from Table where ID = 1,2,3,4,5,6
doesn't work. I would have to do where ID = 1 or ID = 2 or ID = 3 etc etc
How do you do you use a comma delineated list to select multiple values using one where clause?
Upvotes: 0
Views: 1075
Answers (7)
Reputation: 60594
SELECT * FROM Table WHERE Id IN (1,2,3,4,5,6)
Note that you can't insert the entire list in a parametrized query - for example, WHERE Id IN (?) with a parameter containing '1,2,3,4,5,6' will not yield the results you're after.
A nice way to avoid building your SQL string dynamically (and potentially exposing yourself to SQL Injection) is to dynamically build the number of parameters, and then concatenate them into your SQL.
A full example with Python and SQLite (although this method can be used in any language with any SQL database engine):
ids = [1, 2, 3, 4, 5, 6]
params = tuple(ids) # because sqlite wants the parameters in tuple format
paramstring = ', '.join(['?' for dummyp in ids])
# paramstring now contains '?, ?, ?, ?, ?, ?'
# - the same number of '?' as the number of ids
sql = 'SELECT * FROM Table WHERE Id IN (' + paramstring + ')'
# sql now contains 'SELECT * FROM Table WHERE Id IN (?, ?, ?, ?, ?, ?)'
conn = sqlite3.connect(':memory:')
cursor = conn.execute(sql, params)
# You can now iterate through the cursor to get your data
Upvotes: 0
Reputation: 39808
SELECT * FROM myTable WHERE id BETWEEN 1 AND 6
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between
Upvotes: 0
Reputation: 433
if you are looking for dynamic query then
execute ('select * from table where ID IN (1,2,3,4,5,6)')
otherwise
select * from table where ID IN (1,2,3,4,5,6)
will do the job
Upvotes: 0
Reputation: 6867
Try using the in operator select * from Table where ID in (1,2,3,4,5,6) works with delete too
Upvotes: 0
