sql - 将列复制到具有重复值的另一行

Question

我有一个看起来像这样的表格。

ID   Name    Country  Published
1    Madrid           0
2    Madrid  Spain    1
3    Paris   France   0
4    London  England  0

在任何情况下，只要名称相同，我都需要将国家/地区从 Published=1 条目复制到 Published=0 条目。我一直是这样手工完成的:

UPDATE mytable SET Country=(SELECT Country FROM mytable  WHERE ID = 2) WHERE ID=1

但是有没有办法自动执行此操作，而不是手动检查每个条目？

Answer 1

通过连接来实现:

DECLARE @t TABLE
    (
      ID INT ,
      Name VARCHAR(20) ,
      Country VARCHAR(20) ,
      Published BIT
    )

INSERT  INTO @t
VALUES  ( 1, 'Madrid', NULL, 0 ),
        ( 2, 'Madrid', 'Spain', 1 ),
        ( 3, 'Paris', 'France', 0 ),
        ( 4, 'London', 'England', 0 )


UPDATE t1 SET Country = t2.Country
FROM @t t1
JOIN @t t2 ON t1.Name = t2.Name AND t2.Published = 1
WHERE t1.Country IS NULL AND t1.Published = 0

SELECT * FROM @t

输出:

ID  Name    Country Published
1   Madrid  Spain   0
2   Madrid  Spain   1
3   Paris   France  0
4   London  England 0