mySQL: Populate row with data from previous row

Question

I have data that looks roughly like this:

user_id   range   start   end
10        500     1       500
11        175
12        200

and need to transform it into something like this:

user_id   range   start   end
10        500     1       500
11        175     501     675
12        200     676     875

so each subsequent row's start number is the previous row's end number. the current row's end number is the start number + range-1.

I was trying to do this purely in SQL but seem a bit stuck as this produces an error:

UPDATE  users U
SET     start = ( SELECT end+1 FROM users U2 WHERE U2.id = U.id - 1 )
WHERE   U.id > 1;

which results in You can't specify target table 'users' for update in FROM clause -- and while I'm still playing around for a solution, seem unable to find one.

score 1 · Accepted Answer · edited May 23 '17 at 12:27

Look at this answer

Basically, change your subquery portion with a subquery

(SELECT end+1 FROM (SELECT end, id FROM users) U2 WHERE U2.id = U.id - 1)

It is dirty, but it should work. Be warn that it will be slow as hell if your tables are big (and depending on some other config values on your mysql server), and I do not recommend using this on production code. Also, you are asumming you have all subsequent user ids, wich may not be the case. If you have gaps on your ids, it will set null on the field or it may fail.

If you have gaps on your IDs you could do something like this:

(SELECT end+1 FROM (SELECT end, id FROM users) U2 WHERE U2.id < U.id LIMIT 1)

score 1 · Answer 2 · answered Sep 14 '12 at 16:24

It would be a slow query if you had large tables, but you might be able to solve this using a cross join.

UPDATE users U CROSS JOIN users U2
SET U.start = U2.end WHERE U.id = U2.id - 1;

I would maybe consider the idea of having an additional field which references the record containing the end value used for its start value. That would allow for an efficient self-join update and solve problems when you have gaps in your autoincrement. The query might look like

UPDATE users U INNER JOIN users U2 on U.prev_id = U2.id
SET U.start = U2.end;

mySQL: Populate row with data from previous row

2 Answers2