更新表1备注字段与表2中的值在一对多关系

问题描述：

我有2个表我想用表2中的值更新表1中的一列，其中id = id。然而表2有许多行匹配的表1和表2中的所有行需要在表进行更新，以1行1更新表1备注字段与表2中的值在一对多关系

表-A

id | all_names  | 
---+-----------------+ 
1 |AB CD FG HI  | 
2 |     |

** **表-B

id | name | 
---+-------+ 
1 |  | 
2 | Jon | 
2 | Mike |

更新后的表1应该像

id | all_names  | 
    ---+-----------------+ 
    1 |AB CD FG HI  | 
    2 |Jon Mike   |

我试图

update a 
set a.all_names = TRIM(a.all_names) + b.name + ' ' 
from table_a a, table_b b 
where a.id = b.id

所有我最终得到的是在表-A

任何想法空all_names？

[不良习惯踢：使用旧样式的JOIN（http://sqlblog.com/ blogs/aaron_bertrand/archive/2009/10/08/bad-habits-to-kick-using-old-style-joins.aspx） - 旧式*逗号分隔的table * style列表被替换为* proper * ANSI - ** 92 ** SQL标准中的ANSI'JOIN'语法（** 25年前**），其使用不鼓励 –

答

我最终什么事做

Declare @Crs cursor as select * from Table_B; //Temp Table 
open @crs; 
while fetch @crs do 
update Table_A set all_names=ifnull(Table_B,'')+trim(@crs.name)+' ' where 
[email protected]; 
end while; 
close @crs;

它使用最少的线条和优雅

答

除了通过循环之外，我无法真正看到其他任何方式。

DECLARE @id int 
DECLARE @name varchar(50) 

SELECT * INTO #temp FROM TABLE_B 

WHILE EXISTS (SELECT 1 FROM #temp) 
BEGIN 
SELECT @id = (SELECT TOP 1 id from #temp) 
SELECT @name = (SELECT TOP 1 [name] from #temp where id = @id) 

UPDATE A 
SET all_names = LTRIM(RTRIM(all_names + CHAR(32) + @name)) 
FROM Table_A A 
WHERE A.id = @id 

DELETE FROM #temp WHERE id = @id and [name] = @name 

END 

DROP TABLE #temp

查询表把B的内容到一个临时表，并且一旦它已经用来将删除该行。所以基本上所有的名字都通过循环为自己的ID保留相同的值，除了空格+每次添加下一个名字。我已经在更新中添加了修剪以防止前导/尾随空格。

答

我不知道这是否有帮助，但这是严格使用SQL的Oracle版本。你没有提到它在您的要求，但第二合流防止行中重复的条目：

创建表并插入示例行

DROP TABLE table_a; 
DROP TABLE table_b; 

CREATE TABLE table_a 
(
    id INTEGER 
    , all_names VARCHAR2 (128) 
); 

CREATE TABLE table_b 
(
    id INTEGER 
    , name VARCHAR2 (10) 
); 

INSERT INTO table_a (id, all_names) 
    VALUES (1, 'AB CD FG HI'); 

INSERT INTO table_a (id, all_names) 
    VALUES (2, NULL); 

INSERT INTO table_b (id, name) 
    VALUES (1, NULL); 

INSERT INTO table_b (id, name) 
    VALUES (2, 'Jon'); 

INSERT INTO table_b (id, name) 
    VALUES (2, 'Mike'); 

COMMIT;

合并允许重复

MERGE INTO table_a ta 
    USING (SELECT DISTINCT id, LISTAGG (name, ' ') WITHIN GROUP (ORDER BY name) OVER (PARTITION BY id) names 
       FROM table_b) tb 
     ON (ta.id = tb.id) 
WHEN MATCHED 
THEN 
    UPDATE SET all_names = all_names || tb.names 
WHEN NOT MATCHED 
THEN 
    INSERT  (
       ta.id, ta.all_names 
       ) 
     VALUES (
        tb.id, tb.names 
       ); 

SELECT * 
    FROM table_a; 

ROLLBACK;

合并消除重复项

MERGE INTO table_a ta 
    USING (SELECT DISTINCT id, LISTAGG (name, ' ') WITHIN GROUP (ORDER BY name) OVER (PARTITION BY id) names 
       FROM (WITH 
         aset 
         AS 
          (SELECT id, TRIM (all_names) || ' ' AS all_names 
           FROM table_a), 
         bset (id, name, REMAINDER) 
         AS 
          (SELECT id 
            , SUBSTR (all_names, 1, INSTR (all_names, ' ') - 1) name 
            , SUBSTR (all_names, INSTR (all_names, ' ') + 1) REMAINDER 
           FROM aset 
          UNION ALL 
          SELECT id 
            , SUBSTR (REMAINDER, 1, INSTR (REMAINDER, ' ') - 1) name 
            , SUBSTR (REMAINDER, INSTR (REMAINDER, ' ') + 1) REMAINDER 
           FROM bset 
           WHERE name IS NOT NULL) 
        SELECT id, name 
         FROM bset 
        WHERE name IS NOT NULL 
        UNION 
        SELECT id, name 
         FROM table_b 
        WHERE name IS NOT NULL)) tb 
     ON (ta.id = tb.id) 
WHEN MATCHED 
THEN 
    UPDATE SET all_names = tb.names 
WHEN NOT MATCHED 
THEN 
    INSERT (ta.id, ta.all_names) 
    VALUES (tb.id, tb.names); 

SELECT * 
    FROM table_a; 

--ROLLBACK;

更新表1备注字段与表2中的值在一对多关系

相关推荐