MYSQL: How to update multiple columns with same value

Question

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?

pradeepcep · Accepted Answer · 2014-12-03 17:58:56Z

2

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 :-)

answered Dec 3, 2014 at 17:58

pradeepcep

9422 gold badges8 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

kiwisan Over a year ago

Does this solution prevent the possible left-right evaluation issue @a1ex07 warned of? I found another example: bugs.mysql.com/bug.php?id=52861 .

pradeepcep Over a year ago

From the docs: "As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement." And also, "For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed."

pradeepcep Over a year ago

That being said, this particular statement seems to evaluate from left-to-right in Maria-DB 5.5.36. For example, something like UPDATE eventlog SET downtime = @x, downtime_adj = @x := TIMEDIFF(up_stamp,down_stamp) resulted in NULL values for the downtime column. I would say there is no way around undefined behavior (that I know of). The best bet is to test it on your database.

Nico · Accepted Answer · 2014-12-03 17:45:02Z

1

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.

answered Dec 3, 2014 at 17:45

Nico

7,0435 gold badges27 silver badges35 bronze badges

1 Comment

a1ex07 Over a year ago

Docs says "Single-table UPDATE assignments are generally evaluated from left to right. " (dev.mysql.com/doc/refman/5.5/en/update.html). Personally I wouldn't take chances and assume that "generally" means "always"...

Collectives™ on Stack Overflow

MYSQL: How to update multiple columns with same value

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related