Sql表在单个T-Sql中需要多个col更新 [英] Sql table needs multiple col update in a single T-Sql
本文介绍了Sql表在单个T-Sql中需要多个col更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一张表说tbl_a,比如3 cols W,X,Y,Z
。我可以说表数据是这样的:
W X Y Z
1 A L U
2 B L V
3 C L W
5空L U
6空L M
7空L空
5,6,7
被复制 1,2,3
。所以所有列中的数据都应该相同。不幸的是,购买了一些错误,一些记录就这样结束了。是否有任何查询可以在一段时间内复制不匹配的cols?
plz advice ...
解决方案
< blockquote>我所知道的是通过对这2个条件使用2个临时表逐个更新列
选择 * 进入 #a 来自 tbl_a where w in ( 1 , 2 , 3 )
选择 * into #b 来自 tbl_a 其中 w in ( 5 , 6 , 7 )
update #b
s et x = ax,
y = ay,
z = az
来自 #aa
其中 a.w + 4 = bw
要更新单个查询中的记录,请尝试以下操作:
更新 t1
SET t1.x = t2.x
t1.y = t2.y
t1.z = t2.z
FROM (
SELECT *,ROW_NUMBER() OVER ( ORDER BY w) AS RowNo
FROM table1
WHERE w IN ( 5 , 6 , 7 )
) AS t1INNER JOIN (
SELECT * ,ROW_NUMBER() OVER ( ORDER BY w) AS RowNo
FROM table1
WHERE w IN ( 1 , 2 , 3 )
) AS t2 ON t1.RowNo = t2.RowNo
I have a table say tbl_a with say 3 cols W,X,Y,Z
. i can say the table data will be like this:
W X Y Z
1 A L U
2 B L V
3 C L W
5 Null L U
6 Null L M
7 Null L Null
in this rows 5,6,7
are copied 1,2,3
. So both data in all columns should be same. Buy unfortunately some mistake hapend and some records ended like this. Is there any query to copy the mismatch cols in a single stretch?
plz advice...
解决方案
What i know is to update columns one by one by using 2 temp tables for these 2 condiitons
select * into #a from tbl_a where w in(1,2,3) select * into #b from tbl_a where w in(5,6,7) update #b set x=a.x, y=a.y, z=a.z from #a a where a.w+4=b.w
To update records in a single query, try something like this:
UPDATE t1 SET t1.x = t2.x t1.y = t2.y t1.z = t2.z FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY w) AS RowNo FROM table1 WHERE w IN (5,6,7) ) AS t1 INNER JOIN ( SELECT *, ROW_NUMBER() OVER(ORDER BY w) AS RowNo FROM table1 WHERE w IN (1,2,3) ) AS t2 ON t1.RowNo = t2.RowNo
这篇关于Sql表在单个T-Sql中需要多个col更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文