0

I have 100 rows that I need deleted, based on a PersonId and CarModel. Some PersonId could have multiple CarModels, so if I use 

DELETE FROM myTable
WHERE PersonId IN (
    101,
    102,
    103,
    104
)
AND CarModel IN (
    'Honda Accord',
    'Ford Explorer',
    'Other car model',
    'Last car model'
)

Then that will have false positives of delete 101, 'Ford Explorer' which I don't want. only 101, 'Honda Accord' and 102, 'Ford Explorer'

I could format it as

DELETE FROM myTable
WHERE 
(PersonId = 101 AND CarModel = 'Honda) Accord' 
OR (PersonId = 102 AND CarModel = 'Ford Explorer')
OR (PersonId = 103 AND CarModel = 'model 1')
OR ( PersonId = 104 AND CarModel = 'model 2')

But that's kinda ugly. Is there cleaner syntax? I can't find a multi-column where clause for big lists

greg b
  • 27
  • 6
  • 1
    Does this answer your question? [SQL multiple columns in IN clause](https://stackoverflow.com/questions/13027708/sql-multiple-columns-in-in-clause) – greg b Jan 01 '20 at 00:07

1 Answers1

1

Some versions of SQL (MySQL, Oracle) support multiple columns in an IN expression:

DELETE FROM myTable
WHERE (PersonId, CarModel) IN ((101, 'Honda Accord'), (102, 'Ford Explorer'), (103, 'model 1'), ...)
Nick
  • 138,499
  • 22
  • 57
  • 95