将查询结果集更新到另外一个字段中

Question

需要将一个查询的结果集存放到另外一个表的字段中
需求如下：有2个表：表1、表2
表1结构：
Id Title
1 A
2 B
3 C
4 D
5 E
表2结构：
Id Title Table1Id
1 ABC 0
2 AB  0
3 CD  0现在需要做的是
如果表1中Title字段中的值有包含在表2字段的Title中的，那么将表1中的Id号存放到表2的Table1Id中，并通过分号连接各个Id号。需要实现的结果为：
表2：
Id Title Table1Id
1 ABC 1;2;3
2 AB  1;2
3 CD  3;4尝试用
update [表2] set Table1Id = (Select Id from [表1] where Title Like '%' + [表2].Title+ '%')
会出现错误提示
“子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。”请高手指导一下，可以用哪些方法实现？

Answer 1

--写个函数处理吧，想不到什么方法
set nocount on;
if object_id('t1') is not null drop table t1;
create table t1(id int,title varchar(5));
if object_id('t2') is not null drop table t2;
create table t2(id int,title varchar(100),tableid varchar(100));
insert into t1 select 1,'A' union select 2,'B' 
union select 3,'C' union select 4,'D' union select 5,'E';
insert into t2 select 1,'ABC','' union select 2,'AB','' union select 3,'CD',''
if object_id('t_f') is not null drop function t_f;
go
create function dbo.t_f(@title varchar(100))
returns varchar(100)
as
begin
declare @count int
select @count=1
declare @retval varchar(100);
while @count<=len(@title)
begin
set @retval=isnull(@retval,'')+(select convert(varchar(10),id)+';' 
from t1 where title=substring(@title,@count,1))
select @count=@count+1;
end
return @retval
end
go
update t2 set tableid=dbo.t_f(title);
select * from t2;
/*
id          title      tableid
----------- ---------- ----------
1           ABC        1;2;3;
2           AB         1;2;
3           CD         3;4;
*/

Answer 2

--CREATE TABLE t1
 --(Id INT,Title VARCHAR(2))
 --INSERT INTO t1
 --SELECT 1, 'A'
 --UNION ALL 
 --SELECT 2, 'B'
 --UNION ALL 
 --SELECT 3, 'C'
 --UNION ALL 
 --SELECT 4, 'D'
 --UNION ALL 
 --SELECT 5, 'E'
 
 --CREATE TABLE t2
 --(Id INT,Title VARCHAR(10), Table1Id INT )
 --INSERT INTO t2
 --SELECT 1, 'ABC', 0
 --UNION ALL 
 --SELECT 2, 'AB', 0
 --UNION ALL 
 --SELECT 3, 'CD', 0
 --SELECT ROW_NUMBER()OVER(PARTITION BY b.id ORDER BY b.id ) id,b.id bid,b.Title,b.table1id, CONVERT(VARCHAR(128),a.id) aid
 --FROM t2 b LEFT JOIN t1 a ON b.Title LIKE '%' + a.Title+ '%'
 
 
 
 ;with cte as(SELECT ROW_NUMBER()OVER(PARTITION BY b.id ORDER BY b.id ) id,b.id bid,b.Title,b.table1id, CONVERT(VARCHAR(128),a.id) aid
 FROM t2 b LEFT JOIN t1 a ON b.Title LIKE '%' + a.Title+ '%')
 ,cte1 as
 (select id, bid,Title,table1id,cast(aid as nvarchar(100))aid from cte where id=1 
 union all 
 select a.id, a.bid,a.Title,a.table1id,cast(b.aid+';'+a.aid as nvarchar(100)) from cte a join cte1 b on a.bid=b.bid and a.id=b.id+1)
 --SELECT * FROM cte1
 select bid,Title,table1id,aid INTO #t  from cte1 a where id=(select max(id) from cte  where bid=a.bid)  order by bid option (MAXRECURSION 0)
 /*
 bid         Title      table1id    aid
 ----------- ---------- ----------- ----------------------------------------------------------------------------------------------------
 1           ABC        0           1;2;3
 2           AB         0           1;2
 3           CD         0           3;4
 
 (3 行受影响)
 */
 
 UPDATE a
 SET a.table1id=b.table1id
 FROM t2 a INNER JOIN #t b ON a.id=b.bid

Answer 3

DECLARE @t1 TABLE
(
ID VARCHAR(50),
Title VARCHAR(10)
);if object_id('tempdb..#t') is not null
    drop table #t
if object_id('tempdb..#t2') is not null
    drop table #t2
CREATE TABLE #t2
(
ID VARCHAR(50),
Title VARCHAR(10),
TableId VARCHAR(10)
);
INSERT INTO @t1
SELECT '1','A'
UNION 
SELECT '2','B'
UNION 
SELECT '3','C'
UNION 
SELECT '4','D'
UNION 
SELECT '5','E'INSERT INTO #t2
SELECT '1','ABC','0'
UNION
SELECT '2','AB','0'
UNION
SELECT '3','CD','0'----准备数据完毕。开始查询
;WITH c1 AS
(
SELECT t2.Title,t1.ID
  FROM #t2 t2
  JOIN @t1 t1 ON CHARINDEX(t1.Title, t2.Title, 0)<>0 
)
SELECT c1.Title 'Title',STUFF((SELECT ';'+LTRIM(ID) 
FROM c1 tempC
WHERE title=c1.title 
FOR XML PATH('')),1,1,'') AS 'UnionRow'
INTO #t
FROM c1
GROUP BY c1.Title
 
UPDATE t2  --查询出来的结果集更新到临时表#t2
SET t2.TableId = t.UnionRow
FROM #t t JOIN #t2 t2 on t.Title=t2.TitleSELECT * FROM #t2

Answer 4

DECLARE @t1 TABLE
(
ID VARCHAR(50),
Title VARCHAR(10)
);if object_id('tempdb..#t') is not null
    drop table #t
DECLARE @t2 TABLE
(
ID VARCHAR(50),
Title VARCHAR(10),
TableId VARCHAR(10)
);
INSERT INTO @t1
SELECT '1','A'
UNION 
SELECT '2','B'
UNION 
SELECT '3','C'
UNION 
SELECT '4','D'
UNION 
SELECT '5','E'INSERT INTO @t2
SELECT '1','ABC','0'
UNION
SELECT '2','AB','0'
UNION
SELECT '3','CD','0'----准备数据完毕。开始查询
;WITH c1 AS
(
SELECT t2.Title,t1.ID
  FROM @t2 t2
  JOIN @t1 t1 ON CHARINDEX(t1.Title, t2.Title, 0)<>0 
)
SELECT c1.Title 'Title',STUFF((SELECT ';'+LTRIM(ID) 
FROM c1 tempC
WHERE title=c1.title 
FOR XML PATH('')),1,1,'') AS 'UnionRow'
INTO #t
FROM c1
GROUP BY c1.Title
 
UPDATE t2  --查询出来的结果集更新到临时表#t2
SET t2.TableId = t.UnionRow
FROM #t t JOIN @t2 t2 on t.Title=t2.TitleSELECT * FROM @t2--应该换成表变量来处理的