CODEWITHSUNDEEP
mysql
kiwisan
kiwisan

Reputation: 489

MYSQL: How to update multiple columns with same value

I have an update query that calculates a TIMEDIFF on a large number of DATETIME records:

UPDATE eventlog SET downtime = TIMEDIFF(up_stamp,down_stamp);

This works, but I need to set this TIMEDIFF value to another column as well as downtime. Something like this (doesn't work):

UPDATE eventlog SET downtime, downtime_adj = TIMEDIFF(up_stamp,down_stamp);

I know I can combine them like this:

UPDATE eventlog SET downtime = TIMEDIFF(up_stamp,down_stamp), downtime_adj = TIMEDIFF(up_stamp,down_stamp);

But this will recalculate the same time diff 2x as many times as needed. What's the most efficient way to do this?

Upvotes: 0

Views: 2926

Answers (2)

pradeepcep
pradeepcep

Reputation: 932

It appears that it is possible to use a user-defined variable, so that TIMEDIFF() needn't be run twice.

Try this:

UPDATE eventlog SET downtime = @x := TIMEDIFF(up_stamp,down_stamp), downtime_adj = @x;

Here is the documentation: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Disclaimer:

I just happened to read up after seeing your question. I am not proficient in SQL.

Hope that helps :-)

Upvotes: 2

Nico
Nico

Reputation: 6893

I just tested this and it seems to work on my mysql :

UPDATE eventlog SET downtime = TIMEDIFF(up_stamp,down_stamp), downtime_adj = downtime;

downtime will be set with the timediff and downtime_adj will use the new downtime as it's value.

Upvotes: 1

Related Questions