2

Having the following table snippet:

CREATE TABLE `some_table` (
    `exp_col_1` TINYINT(1) NOT NULL DEFAULT '0',
    `mark_col_1_as_exported` TINYINT(1) DEFAULT '0',
    `exp_col_2` TINYINT(1) DEFAULT '0',
    `mark_col_2_as_exported` TINYINT(1) DEFAULT '0',
);

is there a way to write one query for:

  • update the column mark_col_1_as_exported where exp_col_1=1
  • update the column mark_col_2_as_exported where exp_col_2=1

I know I can write an UPDATE for each of them but this is just a snippet, we are talking here about 20 cols to be updated

1 Answer 1

1

Using case would be an option :

UPDATE some_table
  SET mark_col_1_as_exported = CASE WHEN exp_col_1 = 1 THEN 0 ELSE mark_col_1_as_exported END , 
      mark_col_2_as_exported = CASE WHEN exp_col_2 = 2 THEN 0 ELSE mark_col_2_as_exported END 
  WHERE exp_col_1=1 OR exp_col_2=2;

It might have some performance issues because of the usage of or operator.

Note. Please use it on an testig environment first

See example

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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