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

Question

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.

Gordon Linoff · Accepted Answer · 2014-10-28 18:06:01Z

1

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);

answered Oct 28, 2014 at 18:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Data Over a year ago

I hope you don't think of this as an obtuse request, but would you mind explaining the statement, so I can understand this join stuff, it befuddles my brain; if not, no worries.

Gordon Linoff Over a year ago

This is not the forum for explaining how joins work. Try google'ing or reading database documentation.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related