Re: using the same field to be set in the update query and in the where clause

On Fri, 10 Mar 2017 09:06:59 +0100, Axel Schwenke wrote:
> On 09.03.2017 17:01, Peter H. Coffin wrote:
>> On Wed, 8 Mar 2017 02:24:00 -0800 (PST), kushal bhattacharya wrote:
>>> update msg_table set state=? where (msg_id=? and (sock_fd=? and state=?)
>>
>> I'm pretty sure MySQL won't do the work of updating a row where nothing
>> actually changes.
>
> That's nothing you could deduce from that SQL fragment. There are a total of
> 4 place holders in that query and it's quite possible that #1 and #4 will
> get different values on execution.
>
> And even if they had the same values, i.e.
>
> update msg_table set state=1 where (msg_id=42 and (sock_fd=4711 and state=1)
>
> that would be a perfectly legal SQL statement. It would have no effect and
> would always return "0 rows updated". But it's still legal.

Right, but the unstated thing that kushal wanted to do was trip a a timestamp defined ON UPDATE CURRENT_TIMESTAMP. And if the row doesn't in fact update ("0 rows updated") then what I remember (not) happening is that the ON UPDATE doesn't trip and the timestamp is not updated.

-- 
_  o
 |/)