CODEWITHSUNDEEP
mysql
joe
joe

Reputation: 73

column update with other column in mysql on specific row

id  craeted_date         updated_date
121 1556192726251       1556773299198
35  1556192726254       1556781196570
325 1556192726253       1556781296607
365 1556192726256       1556781346627
398 1556192726258       1556781496675

I want to select table table like below second row of craeted_date is updated by first row of updated_date and 3rd row of craeted_date is updated by 2nd row of updated_date like this 

id  craeted_date         updated_date
121 1556192726251       1556773299198
35  1556773299198       1556781196570
325 1556781196570       1556781296607
365 1556781296607       1556781346627
398 1556781346627       1556781496675

am using below query its update all column update user_tbl set craeted_date=updated_date but i need to modify craeted_date second row

Upvotes: 1

Views: 79

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

If you are using MySQL 8+, then we can take advantage of the LAG analytic function:

UPDATE yourTable
SET craeted_date = LAG(updated_date, 1, craeted_date) OVER (ORDER BY craeted_date);

On earlier versions of MySQL, the following update join might work:

UPDATE yourTable t1
INNER JOIN
(
    SELECT craeted_date, updated_date
    FROM (
        SELECT t1.craeted_date,
            COALESCE((SELECT t2.updated_date
             FROM yourTable t2
             WHERE t2.craeted_date < t1.craeted_date
             ORDER BY t2.craeted_date DESC
             LIMIT 1), t1.craeted_date) updated_date
         FROM yourTable t1
    ) t
) t2
    ON t1.craeted_date = t2.craeted_date
SET t1.craeted_date = t2.updated_date;

Upvotes: 1

Related Questions