0

Question extracted from mysql update multiple columns with same now()

Second question why this query doesn't update the columns:

mysql> update table set last_update=last_monitor=now() where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Created a fiddle to illustrate the confusion.

create table t1 (
  c1 INT NOT NULL,
  c2 INT NOT NULL
);
insert into t1 values (0,0);

update t1 set c1=c2=1;

select * from t1;
| c1 | c2 |
|----|----|
|  0 |  0 |

I am expecting c1 and c2 to be set to 1 or the query to fail due to syntax error.

Actual result is that the query is succeeding without updating the columns, so c1 or c2 remain at initial value of 0

As the current behavior does not makes sense to me, I'm definitely missing something. Can someone share some light on how this expression gets evaluated by MySQL engine (or any other SQL engine) ?

Community
  • 1
  • 1
Radu Maris
  • 5,648
  • 4
  • 39
  • 54

2 Answers2

2

I am expecting c1 and c2 to be set to 1 or the query to fail due to syntax error.

Neither is true.
The expression:

c1=c2=1

is evaluated like:

c1=(c2=1)

For MySql the boolean expression c2=1 evaluates to 0 or 1 for false or true respectively.
So 0 or 1 will be assigned to c1 and in this case since c2=1 is false the result will be 0.
You must use separate assignments to each column:

update t1 
set 
  c1=1,
  c2=1;
forpas
  • 160,666
  • 10
  • 38
  • 76
  • One can always do `update t1 set c1=1, c2=c1;` (see https://stackoverflow.com/a/3802797/207603). Glad you gave the inside on how the expression is evaluated as that was exactly what I was looking for, even do I now realized did not explicitly asked for it (update the question now). – Radu Maris May 24 '19 at 07:42
0

Execute this:

  SELECT c2=1 FROM t1

Now grab a cup of coffee and have a good think.

Strawberry
  • 33,750
  • 13
  • 40
  • 57