MySQL/MariaDB: Using expressions and conditions in Update

I ran a few update queries against a MySQL database. Update command said it updated some records. But data didn’t seem to be as we expect. Let’s say the table looked like below.

I ran a query which is similar to below.

UPDATE update_test SET age = 30 AND name = 'John' WHERE id = 1;

MySQL prompt says that it managed to update one row. But if you look at the data after the update query, it will be clear that age is not 30 or neither the name is ‘John’. This is because the query is interpreted as an expression.

UPDATE update_test SET age = (30 AND name = 'John') WHERE id = 1;

Let’s look at another query.

UPDATE update_test SET age = 30 AND name = 'Larry' WHERE id = 3;

The output says it didn’t update any records because age is already 0 and our update query is actually trying to set it as 0. Replacing AND with “,” will set both fields.

UPDATE update_test SET age = 30, name = 'Larry' WHERE id = 3;

With this expression evaluation, we can write advance update queries. For example, I am going to update student data. My update query should update the highest_mark field from the highest value of eng_marks or sci_marks field. Both eng_marks and sci_marks should be higher than 50 too. The query looks like below.

UPDATE students SET highest_mark = IF(eng_marks > sci_marks, eng_marks, sci_marks) WHERE eng_marks > 50 AND sci_marks > 50;

Thanks a lot, Ross for helping to figure this out!

Tags

• mariadb
• mysql