MySQL: how to update with a filtering vector list?

Question

I have a list of values that I want to match with the where clause to update a flag.

(col1, col2, col3)

(1, 2, 3)

(2, 3, 4)

(3, 4, 5)

If the filtering list were made by only one column I would have done this way:

update mytable 
set flag=1
where col1 in ('1','2','3','5','8','13');

Would this query be fit for my purpose?

update mytable 
set flag=1
where col1 in ('1','2','3')
and col2 in ('2','3','4')
and col3 in ('3','4','5');

If the table had a record (1,3,5) the query would update the flag but the filtering list would not be considered correctly.

Is it possible to use the where clause with all of the columns of my filtering list? For example

where (col1, col2, col3) in (???)

Fabian S. · Accepted Answer · 2018-03-17 19:04:29Z

2

You can use IN to compare row constructors (see here).

The following should work:

UPDATE mytable
SET flag=1
WHERE (col1, col2, col3) IN ((1, 2, 3), (2, 3, 4), ...);

But as written here it might not be the fastest solution.

answered Mar 16, 2018 at 18:36

Fabian S.

766 bronze badges

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

1 Answer 1