是否可以在MySQL表中向下移动列数据? [英] Is it possible to shift column data downwards in a MySQL table?

问题描述

我要使用这样的表:

+------+------------+-----------+
|  id  | col1       | col2      |     
+------+------------+-----------+
|  1   | abc        | mno       |
+------+------------+-----------+
|  2   | def        | pqr       |
+------+------------+-----------+
|  3   | ghi        | stu       |
+------+------------+-----------+

是否可以添加新行并向下移动一列?表格应该看起来像这样...

Is there a way to add a new row and shift a column downwards? Table should look like this...

+------+------------+-----------+
|  id  | col1       | col2      | 
+------+------------+-----------+
|  1   | abc        |           |
+------+------------+-----------+
|  2   | def        | mno       |
+------+------------+-----------+
|  3   | ghi        | pqr       |
+------+------------+-----------+
|  4   | jkl        | stu       |
+------+------------+-----------+

插入了新行，并为[row4，col1] ='jkl'插入了单元格值，但是我想向下移动col2数据，因此[row1，col2]现在变为空.

New row inserted with cell value for [row4, col1] = 'jkl', but i want to shift col2 data downwards, so [row1, col2] now becomes null.

谢谢.

谢谢RADAR& 2unco;发挥了作用，您也可以在同一条语句中移动多列...

Thank you RADAR & 2unco; had a play and you can also shift multiple columns within the same statement...

UPDATE mytable t1
LEFT OUTER JOIN mytable t2 ON T1.id=T2.id + 1
SET t1.col2 = t2.col2, t1.col3 = t2.col3;

推荐答案

使用自连接进行更新

SQL小提琴 http://www.sqlfiddle.com/#!2/b4f6c/1

 UPDATE Table1 T1
 JOIN Table1 T2 
 on T1.id=T2.id+1
 SET T1.col2 =T2.col2 ;

 UPDATE Table1 set col2 =NULL
 Where id=1;

在单个语句中

  UPDATE Table1 T1
  LEFT JOIN Table1 T2 
  on T1.id=T2.id+1
  SET T1.col2 =T2.col2 ;

这篇关于是否可以在MySQL表中向下移动列数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！