CODEWITHSUNDEEP
mysql
Deepak Kishore
Deepak Kishore

Reputation: 138

MySQL Update query is not working with and operator

First query:

update tableA set columnA = 'Hello' and updated_at = now() where id = 10;

Second query:

update tableA set columnA = 'Hello', updated_at = now() where id = 10;

When I execute first query columnA updated as 0, where as second query worked fine and updated as Hello.

Why first query update the table as 0 value.

Upvotes: 0

Views: 103

Answers (2)

Eklavya
Eklavya

Reputation: 18440

@TimBiegeleisen is right, there is only one assigning expression for your first query.

MySQL syntax for SET assignment_list in UPDATE is like 

 assignment [, assignment] ...

every assigning expression for column separated by comma(,)

So, when you have multiple assignment use comma(,) to separate the assignment.

You found more details doc here

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

I think that MySQL's lax syntax is at work here. Consider rewriting your first update as:

UPDATE tableA
SET columnA = ('Hello' AND updated_at = NOW())
WHERE id = 10;

That is, the expression on the RHS being assigned to columnA is actually the AND of a string literal, and an assignment. Check the demo below to verify that this RHS in fact is evaluating to zero.

Demo

As to exactly why this is happening, we would have to lookup MySQL's rules for what happens. But best practice is to just stick with your second update query, which uses correct ANSI syntax.

Upvotes: 3

Related Questions