当两个表之间没有关系时在它们之间更新

问题描述

我需要一个 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;

这篇关于当两个表之间没有关系时在它们之间更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！