1

I have this query to re-order items in a MySQL Table

Update Content.items SET list_order=list_order+1 Where list_order=
(Select list_order-1 From Content.items where id='41e31kufg');

But i get this error:

Error Code: 1093. You can't specify target table 'items' for update in FROM clause

Heres an example of a dataset before re-ordering

rowid    list_order
41e31kufg 1
62g88nfjs 2
99h84mlkd 3
92r63mkvf 4

After reordering:

rowid    list_order
99h84mlkd 1
92r63mkvf 2
41e31kufg 3
62g88nfjs 4

What is the solution for this ?

Koder
  • 1,794
  • 3
  • 22
  • 41
  • 2
    Hava a look at this: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause – genespos Nov 13 '15 at 08:34
  • Please provide some example like sample data and expected output. – Ullas Nov 13 '15 at 08:35
  • @genespos i have seen that thread, i am afraid, i cannot tailor that solution to my needs. I am a total sql noob. – Koder Nov 13 '15 at 08:51

2 Answers2

0

Manage to piece together this solution:

Update Content.items SET list_order=list_order+1 Where list_order>= ? and 
list_order < (Select * From (Select list_order From Content.items where id=?) AS lists);
Update Content.items SET list_order=? WHERE id=?;
Koder
  • 1,794
  • 3
  • 22
  • 41
0

Try this:

UPDATE items 
SET list_order=list_order+1 
WHERE list_order= (
    SELECT (
        SELECT list_order-1 
        FROM (
            SELECT list_order 
            FROM items 
            WHERE id='41e31kufg') AS x))
genespos
  • 3,211
  • 6
  • 38
  • 70
  • Thanks, That 'SELECT'ception looks very odd. How will this query perform ? Can it handle high traffic ? Is there an optimized solution for this ? – Koder Nov 14 '15 at 08:54
  • @koder every subquery slows but you need to try it because if a query works on a primary key is faster. If this way is too slow you'll need to find a way using JOIN as in the link I gave you – genespos Nov 14 '15 at 09:29