是否可以在MySQL表中向下移动列数据? [英] Is it possible to shift column data downwards in a MySQL table?
本文介绍了是否可以在MySQL表中向下移动列数据?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我要使用这样的表:
+------+------------+-----------+
| 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屋!
查看全文