MySQL – Set Column Value if Another Column’s New Value is Different

MySQL

I have a table that has a column mycolumn and a column that has a boolean flag mycolumn_is_new. I want to set the second one to true when an UPDATE query changes the value of the first one. The user can type the same data or new data.

I have come up with this query, but mycolumn_is_new is always set to false :

UPDATE mytable SET
    mycolumn = '1234',
    mycolumn_is_new = IF(mycolumn <> '1234', TRUE, FALSE)
WHERE id = 321

Is it possible check the new and old value in a single query?

Best Answer

All expressions in the SET section are evaluated in the order in which they are written. So mycolumn in the second expression is equal to the value set in the first expression. Simply switch expressions - mycolumn_is_new = first.

Is it possible check the new and old value in a single query?

Yes, using user-defined variable. Save old, assign new, use old from variable and new from field. The simplest task need those method - switch the values of two fields in one UPDATE.

CHANGE #1 : Refactor the Query to the following:

select B.* from
(
    select MIN(id) id FROM MyTable
    where business_key = 1234
) A INNER JOIN MyTable B using (id);

Keep in mind that MAX(id) would be the original (the oldest) record.

CHANGE #2 : Add the following index

ALTER TABLE MyTable ADD INDEX NewerIndx (business_key,id);

If you have queries that have this WHERE clause (WHERE business_key=1234 AND parent=999), then do not drop NewIndx. You may want to drop your old index on a staging or dev server and test all your queries, if any of the queries get worse on the dev server, keep your old index.

Give it a Try !!!

Mysql – the benefit of using BOOLEAN over TINYINT(1)

Those two are really synonyms, so you can use them interchangeably. You won't see any difference between them.

If you want to allow 0 and 1 only, you can still use the bit type.

Best Answer

Related Solutions

MySQL using a index referencing a different column

CHANGE #1 : Refactor the Query to the following:

CHANGE #2 : Add the following index

Mysql – the benefit of using BOOLEAN over TINYINT(1)

Related Question