CODEWITHSUNDEEP
mysqlsql-update
Murdoch
Murdoch

Reputation: 630

MySQL: Incorrect usage of UPDATE and ORDER BY occuring at a position decoration update query

My Ruby plugin acts_as_list for sorting generates gaps in the position column when I insert Thing 2.0 at position 2 of a list from 1-3 and I want to get this list.

formatted_position;position;name
1;1;Thing 1
2;2;Thing 2.0;
3;4;Thing 2
4;5;Thing 3

So I tried ...

UPDATE user_ranking_items JOIN (SELECT @rownum := 0) r SET formatted_position = @rownum := @rownum + 1 WHERE ranking_id = 1 ORDER BY position ASC

But I got the MySQL exception Incorrect usage of UPDATE and ORDER BY.

How to handle this?

P.S.: Selecting works and returns the list from above ...

SELECT position, @rownum := @rownum + 1 AS formatted_position FROM user_ranking_items JOIN (SELECT @rownum := 0) r WHERE ranking_id = 1 ORDER BY position ASC;

Upvotes: 2

Views: 1020

Answers (1)

spencer7593
spencer7593

Reputation: 108480

The "trick" is to wrap your query that's working (with the user variable and the order by) as an inline view (MySQL calls it a derived table). Reference that whole view as a row source in the UPDATE statement.

Since you've already got the query that returns the result you want, you just need to have it return a unique id (e.g. the primary key) so you can do the JOIN between the inline view back to the original row in the target of the UPDATE.

Something like this:

UPDATE user_ranking_items t
  JOIN (  
         -- your query here 
       ) s
    ON s.id = t.id
   SET t.col = s.new_col_val

(This works because MySQL first materializes that inline view, as a temporary MyISAM table, and then the UPDATE references the temporary MyISAM table.)

Upvotes: 5

Related Questions