CODEWITHSUNDEEP
mysql
Data
Data

Reputation: 1357

Is it possible to shift row data in a MySQL table?

I'd like to shift the cell values to the right and then UPDATE [row1, col1].

BEFORE:

+----------------+------------------+------------------+------------------+
| id(VARCHAR 20) | col1(VARCHAR 10) | col2(VARCHAR 10) | col3(VARCHAR 10) |
+----------------+------------------+------------------+------------------+
| row1           | john             | jane             | jeff             |
+----------------+------------------+------------------+------------------+
| row2           | some             | other            | stuff            |
+----------------+------------------+------------------+------------------+

AFTER:

+----------------+------------------+------------------+------------------+
| id(VARCHAR 20) | col1(VARCHAR 10) | col2(VARCHAR 10) | col3(VARCHAR 10) |
+----------------+------------------+------------------+------------------+
| row1           | sue*             | john             | jane             |
+----------------+------------------+------------------+------------------+
| row2           | some             | other            | stuff            |
+----------------+------------------+------------------+------------------+

*Sue has been inserted and the previous row values have been shifted to the right and 'jeff' has been popped off, so to speak, into the aether; the number of columns is fixed.

I've asked a similar question here, but I want to rotate my table design by 90°.

Thanks in advance.

Upvotes: 1

Views: 1013

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This should work for you:

update table t
    set col3 = col2,
        col2 = col1,
        col1 = 'sue'
    where id = 'row1';

Upvotes: 1

Related Questions