如何在sqlserver中更新列值？ [英] How to Update Column values in sqlserver?

问题描述

大家好，

我创建了一个表并填充了一些记录。但是，我错过了在Uid列上填充序列号。



如何在这5条记录（101到105）上填充序列号 - 这里不应使用LOOP ......！



有没有简单的更新？



Uid UName电话



-----------------------------



NULL ABC1 12345



NULL EFG1 67890



NULL JKL1 13567



NULL MNO1 24680



NULL QWS1 67981

Hi All,
I have created a table and populated some records. But, I missed to populate the serial number on Uid column.

How to populate sequence number on these 5 records(101 to 105) - LOOP should not be used here...!

Is there any simple UPDATE ?

Uid UName Phone

-----------------------------

NULL ABC1 12345

NULL EFG1 67890

NULL JKL1 13567

NULL MNO1 24680

NULL QWS1 67981

推荐答案

这是示例代码

Here is a sample code

CREATE TABLE #Table1
(
	Uid INT,
	UName VARCHAR(50),
	Phone VARCHAR(50)
)

INSERT INTO #Table1
SELECT NULL, ''ABC1'', ''12345'' UNION ALL
 
SELECT NULL, ''EFG1'', ''67890'' UNION ALL
 
SELECT NULL, ''JKL1'', ''13567'' UNION ALL
 
SELECT NULL, ''MNO1'', ''24680'' UNION ALL
 
SELECT NULL, ''QWS1'', ''67981''

--Before Updating Uid
SELECT * FROM #Table1;


WITH CTE AS
(
	SELECT ROW_NUMBER() oVER (ORDER BY UName) + 100 AS Id, * FROM #Table1
)


UPDATE T
SET UId = Id
FROM #Table1 T
INNER JOIN CTE ON T.UName = CTE.UName AND T.Phone = CTE.Phone

--Afer Updating Uid
SELECT * FROM #Table1

DROP TABLE #Table1

我得到了解决方案。

I got solution.

Declare @Seq Int

Select @Seq = 100

Update Stdrec Set ID= @Seq, @Seq = @Seq +1

这篇关于如何在sqlserver中更新列值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！