用Table B非空ITEM与Table C非空COLOR更新Table A对应空列

原始数据表 #

Table A #

ID|POS|Location|ITEM |COLOR
------------------------------
1 | 1 |ABC     |A    | RED
1 | 2 |ABC     |B    | BLUE
1 | 3 |ABC     |NULL | YELLOW
1 | 4 |ABC     |D    | NULL
2 | 1 |ABC     |A    | BLACK
2 | 2 |ABC     |B    | BLUE
2 | 3 |ABC     |C    | RED
3 | 1 |ABC     |NULL | BROWN
4 | 1 |ABC     |A    | WHITE
4 | 2 |ABC     |B    | RED
4 | 3 |ABC     |NULL | BLUE
4 | 4 |ABC     |NULL | YELLOW
5 | 1 |ABC     |A    | NULL
5 | 2 |ABC     |C    | NULL
5 | 3 |ABC     |D    | BLUE
6 | 1 |ABC     |A    | RED
6 | 2 |ABC     |B    | BROWN
6 | 3 |ABC     |C    | WHITE
7 | 1 |ABC     |NULL | RED
7 | 2 |ABC     |B    | NULL
7 | 3 |ABC     |C    | YELLOW
8 | 1 |ABC     |A    | NULL
8 | 2 |ABC     |B    | BLACK
8 | 3 |ABC     |C    | BLUE
8 | 4 |ABC     |D    | RED
8 | 5 |ABC     |E    | BROWN
9 | 1 |ABC     |NULL | WHITE
9 | 2 |ABC     |C    | BLUE
9 | 3 |ABC     |D    | YELLOW
9 | 4 |ABC     |E    | NULL
10| 1 |ABC     |A    | NULL
10| 2 |ABC     |B    | WHITE
10| 3 |ABC     |C    | BLACK
11| 1 |ABC     |A    | BLUE
11| 2 |ABC     |B    | NULL

Table B #

ID|POS|Location|ITEM
1 | 1 |ABC     |A
1 | 2 |ABC     |B
1 | 3 |ABC     |B
1 | 4 |ABC     |D
2 | 1 |ABC     |A
2 | 2 |ABC     |B
2 | 3 |ABC     |C
3 | 1 |ABC     |E
4 | 1 |ABC     |A
4 | 2 |ABC     |B
4 | 3 |ABC     |F
4 | 4 |ABC     |NULL
5 | 1 |ABC     |A
5 | 2 |ABC     |C
5 | 3 |ABC     |NULL
6 | 1 |ABC     |A
6 | 2 |ABC     |B

Table C #

ID|POS|Location |COLOR
--------------------------
1 | 1 |ABC      | RED
1 | 2 |ABC      | BLUE
1 | 3 |ABC      | YELLOW
1 | 4 |ABC      | RED
2 | 1 |ABC      | BLACK
2 | 2 |ABC      | BLUE
2 | 3 |ABC      | VIOLET
3 | 1 |ABC      | BROWN
4 | 1 |ABC      | WHITE
4 | 2 |ABC      | RED
4 | 3 |ABC      | BLUE
4 | 4 |ABC      | YELLOW
5 | 1 |ABC      | WHITE
5 | 2 |ABC      | BLACK
5 | 3 |ABC      | BLUE
6 | 1 |ABC      | RED
6 | 2 |ABC      | BROWN
6 | 3 |ABC      | WHITE
7 | 1 |ABC      | RED
7 | 2 |ABC      | BLUE
7 | 3 |ABC      | YELLOW
8 | 1 |ABC      | PURPLE
8 | 2 |ABC      | BLACK
8 | 3 |ABC      | PINK
8 | 4 |ABC      | RED
8 | 5 |ABC      | BROWN
9 | 1 |ABC      | WHITE
9 | 2 |ABC      | BLUE
9 | 3 |ABC      | YELLOW
9 | 4 |ABC      | NULL
10| 1 |ABC      | CYAN
10| 2 |ABC      | WHITE
10| 3 |ABC      | BLACK
11| 1 |ABC      | INDIGO
11| 2 |ABC      | NULL

更新需求 #

仅当Table A的ITEM列为空时，使用Table B中同ID、POS、Location的非空ITEM值进行更新；仅当Table A的COLOR列为空时，使用Table C中同ID、POS、Location的非空COLOR值进行更新。

实现方案 #

我来给你梳理下这个需求的具体实现思路，核心就是精准匹配条件再执行更新，避免误改原有有效数据。下面针对主流数据库给出具体的SQL写法：

MySQL/MariaDB 版本 #

UPDATE Table A
LEFT JOIN Table B ON A.ID = B.ID AND A.POS = B.POS AND A.Location = B.Location
LEFT JOIN Table C ON A.ID = C.ID AND A.POS = C.POS AND A.Location = C.Location
SET 
    A.ITEM = CASE WHEN A.ITEM IS NULL AND B.ITEM IS NOT NULL THEN B.ITEM ELSE A.ITEM END,
    A.COLOR = CASE WHEN A.COLOR IS NULL AND C.COLOR IS NOT NULL THEN C.COLOR ELSE A.COLOR END;

SQL Server 版本 #

UPDATE A
SET 
    ITEM = CASE WHEN A.ITEM IS NULL AND B.ITEM IS NOT NULL THEN B.ITEM ELSE A.ITEM END,
    COLOR = CASE WHEN A.COLOR IS NULL AND C.COLOR IS NOT NULL THEN C.COLOR ELSE A.COLOR END
FROM Table A
LEFT JOIN Table B ON A.ID = B.ID AND A.POS = B.POS AND A.Location = B.Location
LEFT JOIN Table C ON A.ID = C.ID AND A.POS = C.POS AND A.Location = C.Location;

关键说明 #

• 用LEFT JOIN是为了保证：就算Table B或C里没有对应匹配的记录，Table A里的原有数据也不会被破坏
• CASE语句是核心逻辑：只有当Table A的ITEM/COLOR是空值，同时关联表的对应字段非空时，才会用关联表的值去更新，完全贴合需求
• 匹配条件严格绑定ID、POS、Location三个字段，确保更新的是同一位置的对应数据，不会出现串错的情况

内容的提问来源于stack exchange，提问作者Jay2012