-2

I'm trying to write query do delete rows based on two columns:

  DELETE FROM us_TEST 
  WHERE [cell] in ('CA001018611', 'CA001135126')
  AND [date] IN ('2016-01-04', '2016-07-14')

However this matches when [cell] 'CA001018611' is either '2016-01-04' or '2016-07-14'.

How can I modify this to delete 'CA001018611' only when [date] is '2016-01-04' and 'CA001135126' only when [date] is '2016-07-14'

Edit: I have about ~100-300k rows to delete based on similar criteria.

  • Are these values in another table or flat file? How do you plan on feeding them to the delete statement? – MrTCS May 10 '18 at 21:47
  • Or, will the rows to deleted be determined by a query of some sort? (in which case, you could presumably use the query to identify the rows). – RDFozz May 10 '18 at 22:14
  • why downvote ? haven't I understood the question ? – KumarHarsh May 11 '18 at 11:35
4

If you are only looking to delete these rows based on the two conditions listed you can break this out into two separate queries.

DELETE FROM us_TEST WHERE [cell] = 'CA001018611' AND [date] = '2016-01-04';
DELETE FROM us_TEST WHERE [cell] = 'CA001135126' AND [date] = '2016-07-14';

If though, you have your criteria to find your rows to delete in tables there are other options. The table definitions and full requirements would be helpful.

| improve this answer | |
  • This is the only sensible answer – Gaius May 11 '18 at 7:16
4

You need to add an OR to your SQL as follows:

DELETE FROM us_TEST
WHERE ([cell] = 'CA001018611' AND [date] = '2016-04-01')
OR    ([cell] = 'CA001135126' AND [date] = '2016-14-07');
| improve this answer | |
  • If I have ~100k rows to drop would you still recommend this solution? – Rafael May 10 '18 at 21:45
  • Index on [cell], index on [date], or possibly a compound one. Depending on your server's spec, I don't see it as a huge issue. Caveat: Not a SQL Server expert - more of an Open Source man :-) Maybe run a few tests before launching in production? – Vérace May 10 '18 at 21:48
  • @R.M. You could test for yourself. – paparazzo May 10 '18 at 22:05
2

Does this help?

DELETE FROM us_TEST 
WHERE ([cell] = 'CA001018611' AND [date] = '2016-01-04') 
      OR 
      ([cell] = 'CA001135126' AND [date] = '2016-07-14')
| improve this answer | |
2

A join might be the best performance

delete * 
from table t 
join FROM ( values ('CA001018611', '2016-01-04'), ('CA001135126', '2016-07-14') 
          ) q(cl, dt) 
  on t.cl = q.cl 
 and t.dt = q.dt
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.