CODEWITHSUNDEEP
mysql
Data
Data

Reputation: 1357

How to shift column data using UPDATE in a MySQL table?

I want to use a static-sized table like this:

+------+----------+-----------+
|  id  | col1     | col2      |
+------+----------+-----------+
|  1   | a        | x         |
+------+----------+-----------+
|  2   | b        | y         |
+------+----------+-----------+
|  3   | c        | z         |
+------+----------+-----------+

Is there a way to shift the column data upwards when I update [3, col1] for example? Table should look like this...

+------+----------+-----------+
|  id  | col1     | col2      |
+------+----------+-----------+
|  1   | b        | x         |
+------+----------+-----------+
|  2   | c        | y         |
+------+----------+-----------+
|  3   | d*       | z         |
+------+----------+-----------+

*New value in [row3, col1] and column data has been shifted up; thanks in advance.

Upvotes: 0

Views: 197

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

You can do this with an update/join:

update table t left join
       table tnext
       on t.id = tnext.id - 1
    set t.col1 = (case when tnext.id is null then 'd' else tnext.col1 end);

Upvotes: 1

Related Questions