问题描述
我需要一个 SQL 语句,用 #T2(C1) 中的值填充 #T1 表第二列中的空值.
I need a SQL statement which fills the null values from the second column of #T1 table with values from #T2(C1).
这两个表的列之间没有外键或匹配项.
There is no foreign key or match between the columns of those two tables.
示例:
T1 (C1, T2C1) A1, 1 A2, null A3, null A4, 4 A5, null ------------- T2 (C1) a b
更新后,T1 将如下所示:
After update, the T1 will look like:
A1, 1 A2, a A3, b A4, 4 A5, null
我找到了两种方法:
使用 CTE
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') ;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
我的问题是,我可以在不使用窗口函数且不使用光标的情况下实现这一点吗?
My question is, can I achieve this without using windowing functions and with no cursors?
更新
@Damien_The_Unbeliever 正确地指出要进行这种更新,如果不定义表的排序是不可能的,实际上我认为确切地说是没有正确识别和链接目标表中的行.
@Bogdan Sahlean 找到了另一种方法,使用表变量和 IDENTITY 列,我对这个解决方案很满意,这是另一种方法但是,在实际应用中我仍然会使用窗口函数
谢谢大家
Update
@Damien_The_Unbeliever correctly points that to do this kind of update it is not possible without defining an ordering on tables, actually I think exactly said is without properly identify and link the rows from target table.
@Bogdan Sahlean has found another way, using table variables and IDENTITY column, which I'm happy with this solution, it's another way
However, in the real application I will still use the windowing functions
Thanks all
推荐答案
1.我想你在目标表 (#T1) 中有一个 pk.
1.I suppose you have a pk in target table (#T1).
2.该解决方案使用 IDENTITY(1,1) 列和两个表变量代替 ROW_NUMBER.
2.Instead of ROW_NUMBER this solution uses IDENTITY(1,1) columns and two table variables.
3.我没有测试过这个解决方案.
3.I didn't tested this solution.
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;