sql deleting multiple rows of different columns

Question

I am working on a way to delete multiple rows of a single SQL database, targeting two columns. ColumnA will always be the same value, and ColumnB will be different values.

I am trying to figure out the correct syntax for something like this:

DELETE FROM tableName WHERE (
    ColumnA = 123 AND ColumnB = 1,
    ColumnA = 123 AND ColumnB = 2,
    ColumnA = 123 AND ColumnB = 3,
    ColumnA = 123 AND ColumnB = 4,
    ColumnA = 123 AND ColumnB = 8
);

What will be the correct SQL syntax for the above logic?

Oh yeah, you even may not need to add parentheses as ANDs are interpreted before ORs already: dev.mysql.com/doc/refman/8.0/en/operator-precedence.html — vahdet
– vahdet, Commented Jan 7, 2020 at 13:10
Try this, DELETE FROM tableName WHERE ( ColumnA = 123 AND ColumnB in ( 1, 2,3,4,8); — Bharati Mathapati
– Bharati Mathapati, Commented Jan 7, 2020 at 16:55

forpas · Accepted Answer · 2020-01-07 13:06:19Z

10

This will do it:

DELETE FROM tableName 
WHERE ColumnA = 123 AND ColumnB IN (1, 2, 3, 4, 8)

answered Jan 7, 2020 at 13:06

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yogesh Sharma · Accepted Answer · 2020-01-07 13:05:21Z

8

Replace , with OR :

where (ColumnA = 123 and ColumnB = 1) or
      (ColumnA = 123 and ColumnB = 2) or
        . . .
      (ColumnA = 123 and ColumnB = 8);

This can be also simplified as :

where ColumnA = 123 and
      ColumnB in (1, 2, 3, 4, 8);

answered Jan 7, 2020 at 13:05

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2020-01-07 13:05:57Z

7

The simplest solution (as suggested by GMB) is to replace the commas with OR. However, MySQL also supports tuples, so you can use:

WHERE (ColumnA, ColumnB) IN ( (123, 1), (123, 2), (123, 3), (123, 4), (123, 8) )

Or, if the ColumnA values really are all the same, then:

WHERE ColumnA = 123 AND
      ColumnB IN (1, 2, 3, 4, 8)

answered Jan 7, 2020 at 13:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

sql deleting multiple rows of different columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related