昨天的SQL编程中,有使用到一个方法,就是把一个表某一字段更新至另一个表的字段中去。
实现这个方法,Insus.NET有尝试了几个方法,下面一一分享出来,让大家参考参考。
下面的数据只是模拟了,形式与Insus.NET实际数据相似。使用临时表,你真实表一样。
表一:
代码:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE TABLE #Table1 ([Col1] INT NOT NULL PRIMARY KEY,[Col2] VARCHAR(10) NOT NULL,[Col3] VARCHAR(10) NULL) INSERT INTO #Table1 ([Col1],[Col2],[Col3]) VALUES (1,'GF',NULL), (2,'ER',NULL), (3,'SA',NULL), (4,'JK',NULL), (5,'RE',NULL) GO
代码:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE TABLE #Table2 ([Col1] INT NOT NULL,[Col2] VARCHAR(100) NOT NULL) INSERT INTO #Table2 ([Col1],[Col2]) VALUES (3,'TR'), (2,'QT'), (1,'JN'), (5,'LP'), (4,'TR') GO
数据准备好了,看看要求,是需要把表2的Col2列值填充至表一的Col3上,2张表的对应主键均是Col1。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
DECLARE @r INT =1, @rs INT = 0 SELECT @rs = MAX([Col1]) FROM #Table2 WHILE @r <= @rs BEGIN IF EXISTS(SELECT TOP 1 1 FROM #Table2 WHERE [Col1] = @r) BEGIN DECLARE @v VARCHAR(10) SELECT @v = [Col2] FROM #Table2 WHERE [Col1] = @r UPDATE #Table1 SET [Col3] = @v WHERE [Col1] = @r END SET @r = @r + 1 END
代码:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE TABLE #t ([Col1] INT NOT NULL PRIMARY KEY,[Col2] VARCHAR(10) NOT NULL,[Col3] VARCHAR(10) NULL) INSERT INTO #t ([Col1],[Col2],[Col3]) SELECT t1.[Col1],t1.[Col2],t2.[Col2] FROM #Table1 AS t1 INNER JOIN #Table2 AS t2 ON (t1.[Col1] = t2.[Col1])
方法三,就是使用UPDATE ... INNER JOIN ...语法来处理,简单:
代码:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
UPDATE t1 SET t1.[Col3] = t2.[Col2] FROM #Table1 AS t1 INNER JOIN #Table2 AS t2 ON (t1.[Col1] = t2.[Col1])