Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can we reply on the order of columns in an UPDATE?

SET @x = 0
UPDATE table1 SET
col1=IF(col1 IS NULL, NULL, @x:=@x+col1), 
col2=IF(col2 IS NULL, NULL, @x:=@x+col2),
col3=IF(col3 IS NULL, NULL, @x:=0)

Is it guaranteed that col1 is UPDATEd before col2?

I do care about the order in calculating the value @x.

share|improve this question
    
The docs state - Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.. So In stating that multi-table updates are not guaranteed to be in any particular order it seems to imply that single table updates are, however, stating they are "generally evaluated from left to right" doesn't fill me with confidence! –  GarethD Jan 9 at 16:19
    
@GarethD my case is single table, is there a guarantee that UPDATE is from left to right? as we can use ORDER BY for single table, but not multiple-tables? –  All Jan 9 at 16:21
add comment

2 Answers

The order is unreliable and is subject to change on different query evaluation plans.

However, I found a reliable hack around this. Please consider my presentation Programmatic Queries: Things you can Code with MySQL. In particular see slides 10 - 15.

The trick presented utilizes the known order of evaluation in a CASE clause, as follows:

SELECT 
  CASE
    WHEN (@c3 := @c1 + @c2) IS NULL THEN NULL
    WHEN (@c1 := @c2) IS NULL THEN NULL
    WHEN (@c2 := @c3) IS NULL THEN NULL
    ELSE @c3
  END AS seq
FROM 
  mysql.help_topic, 
  (SELECT @c1 := 1, @c2 := 0) s_init
LIMIT 10;

The CASE expression has to evaluate the WHEN clauses by order of appearance (to be more specific, it does so, and it doesn't make sense to do otherwise).

In the above none of the WHEN expressions make for a true value, which is why the CASE clause keeps evaluating them one by one. This gives you your required order.

share|improve this answer
    
I'm still struggling to understand how it works. I made an example on sqlfiddle: sqlfiddle.com/#!2/12b797/5/1 Could you give me a helping hand in getting the same result as the first query in the column group_wise_row_number? The second query is my attempt. Thanks. –  tombom Jan 10 at 16:36
    
Sorry, but I don't know what it is that you are trying to achieve. If you explain that, I can help you. –  Shlomi Noach Jan 10 at 17:27
    
Oh, got it. Looking into. –  Shlomi Noach Jan 10 at 17:28
    
Done. Please see updated fiddle –  Shlomi Noach Jan 10 at 17:31
    
hrrmmmm... overriden. This is the case clause: case when (if(@prev != groups, @row:=1, @row:=@row+1)) is null then null when (@prev := groups) is null then null else @row end –  Shlomi Noach Jan 11 at 4:56
show 1 more comment

To quote the manual:

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. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

Another issue with assigning a value to a variable and reading the value within the same non-SET statement is that the default result type of a variable is based on its type at the start of the statement. The following example illustrates this:

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

For this SELECT statement, MySQL reports to the client that column one is a string and converts all accesses of @a to strings, even though @a is set to a number for the second row. After the SELECT statement executes, @a is regarded as a number for the next statement.

share|improve this answer
    
I totally understand the unreliability you quoted, but for a simple arithmetic mentioned in the question, a guaranteed order of UPDATE can satisfy the user variable re-SET. Ain't it? –  All Jan 9 at 16:45
1  
Personally I can say, that I use user variables very often, for selects as well as updates, and the behaviour has always been as expected. Still I use them just for queries where I can control the result, not in application code, simply because of the above quoted. You can use them and I'd bet you get the correct result, but don't blame me if not. –  tombom Jan 9 at 17:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.