我需要一个SQL语句,用#T2(C1)的值填充#T1表的第二列的空值。
这两个表的列之间没有外键或匹配。
示例:T1 (C1, T2C1)
A1, 1
A2, null
A3, null
A4, 4
A5, null
-------------
T2 (C1)
a
b
更新后,T1看起来像:
A1, 1
A2, a
A3, b
A4, 4
A5, null
我找到了两种方法:
使用CTEcreate table #T1 (C1 varchar(10), T2C1 varchar(10))
create table #T2 (C1 varchar(10))
insert into #T1 values ('A1', '1')
insert into #T1 values ('A2', null)
insert into #T1 values ('A3', null)
insert into #T1 values ('A4', '4')
insert into #T1 values ('A5', null)
insert into #T2 values ('a')
insert into #T2 values ('b')
;with t2 as
(
select C1, row_number() over (order by C1) as Index2
from #T2
)
,t1 as
(
select T2C1, row_number() over (order by C1) as Index1
from #T1
where T2C1 is null
)
update t1
set t1.T2C1 = t2.C1
from t2
where t1.Index1 = t2.Index2
select * from #T1
drop table #T1
drop table #T2
与派生表
create table #T1 (C1 varchar(10), T2C1 varchar(10))
create table #T2 (C1 varchar(10))
insert into #T1 values ('A1', '1')
insert into #T1 values ('A2', null)
insert into #T1 values ('A3', null)
insert into #T1 values ('A4', '4')
insert into #T1 values ('A5', null)
insert into #T2 values ('a')
insert into #T2 values ('b')
update #T1
set T2C1 = cj.C1
from #T1
join (select T2C1, row_number() over (order by C1) as Index1, C1
from #T1
where T2C1 is null) ci on ci.C1 = #T1.C1
join (select C1, row_number() over (order by C1) as Index2
from #T2) cj on ci.Index1 = cj.Index2
select * from #T1
drop table #T1
drop table #T2
我的问题是,我可以不使用窗口函数和没有光标实现这一点吗?
@Damien_The_Unbeliever正确地指出,如果不定义表上的顺序,就不可能进行这种更新,实际上我认为确切地说,没有正确地识别和链接目标表中的行。
@Bogdan Sahlean找到了另一种方式,使用表变量和IDENTITY列,我对这个解决方案很满意,这是另一种方式但是,在实际应用中,我仍然会使用窗口函数
谢谢所有的
1。我假设你有一个pk
在目标表(#T1)。
2。这个解决方案使用IDENTITY(1,1)
列和两个表变量来代替ROW_NUMBER。
3。我没有测试这个解决方案。
DECLARE @t2_count INT = (SELECT COUNT(*) FROM #T2);
DECLARE @Target TABLE
(
MyId INT IDENTITY(1,1) PRIMARY KEY
,T1_pk INT NOT NULL UNIQUE
);
INSERT @Target (T1_pk)
SELECT TOP(@t2_count) pk
FROM #T1
WHERE T2C1 IS NULL;
DECLARE @Source TABLE
(
MyId INT IDENTITY(1,1) PRIMARY KEY
,C1 VARCHAR(10) NOT NULL
);
INSERT @Source (C1)
SELECT C1
FROM #T2;
UPDATE #T1
SET T2C1 = src.C1
FROM #T1 t
INNER JOIN @Target trg ON t.pk = trg.T1_pk
INNER JOIN @Source src ON trg.MyId = src.MyId;
我同意Damien的观点,无论如何,当你依赖引擎并假设表是由C1列排序的(这只取决于DB,你不能依赖它),你可以发出一个更新语句,它将更新所有行
declare @a int
set @a = 0
update #t1
set t2c1 = @a,
@a = @a+1
但是我不会那样做。
我将使用CTE和ROW_NUMBER:
WITH cte_t1 AS
(
SELECT
T2C1
,ROW_NUMBER() OVER (ORDER BY C1) AS id
FROM T1
WHERE T2C1 IS NULL
)
,cte_t2 AS
(
SELECT
C1
,ROW_NUMBER() OVER (ORDER BY C1) AS id
FROM T2
)
UPDATE t1
SET t1.T2C1 = t2.C1
FROM cte_t1 AS t1
INNER JOIN cte_t2 AS t2
ON t1.id = t2.id
;
ROW_NUMBER根据列的顺序创建一个标识符(我假设这是您正在寻找的,但请注意SQL没有顺序,而是这个查询依赖于记录的物理顺序,这不是一个好主意,但看起来这就是您正在处理的)。然后在这个标识符上连接,用T2中的值更新T1中的记录。
我已经把这个解决方案放在SQL Fiddle。