使用另一个表中的非空值更新 sqlite 表

Question

我有两个sqlite表，它们都具有相同的列布局。我想合并两个表(或更新表 1(，如果它们不为 null，则从表 2 中获取值，否则从表 1 中获取它们。有没有更好的方法来做到这一点，除了具有多个case子句的UPDATE(类似于另一个表中的SQLITE UPDATE字段IF NULL(中的方法(？这些表有大量的列，这将使这样的陈述很长。

表1

|-------+-------+--------+----+--------|
| id    | col1  | col2   | .. | col100 |
|-------+-------+--------+----+--------|
| 2346a | apple | red    |    | WA     |
| d27d7 | pear  | green  |    | VA     |
| 568ba | lemon | yellow |    | CA     |
| 9896f | grape | purple |    | CA     |
| 1b7da | peach | pink   |    | CA     |
|-------+-------+--------+----+--------|

表2

|-------+-------+--------+----+--------|
| id    | col1  | col2   | .. | col100 |
|-------+-------+--------+----+--------|
| 2346a | null  | green  |    | null   |
| 1b7da | null  | null   |    | GA     |
| 9896f | plum  | null   |    | null   |
|-------+-------+--------+----+--------|

期望的结果

|-------+-------+--------+----+--------|
| id    | col1  | col2   | .. | col100 |
|-------+-------+--------+----+--------|
| 2346a | apple | green  |    | WA     |
| d27d7 | pear  | green  |    | VA     |
| 568ba | lemon | yellow |    | CA     |
| 9896f | plum  | purple |    | CA     |
| 1b7da | peach | pink   |    | GA     |
|-------+-------+--------+----+--------|

Answer 1

您可以使用coalesce()将table2与left join一起，并优先考虑table2中的非null值：

select
t1.id,
coalesce(t2.col1, t1.col1) col1,
coalesce(t2.col2, t1.col2) col2,
...
coalesce(t2.col100, t1.col100) col100
from table1 t1
left join table2 t2 on t2.id = t1.id

Answer 2

您可以使用ROW VALUES来更新Table1：

update Table1
set (col1, col2, col100) = (
select 
coalesce(t2.col1, Table1.col1),
coalesce(t2.col2, Table1.col2),
................................
coalesce(t2.col100, Table1.col100)
from Table2 t2
where t2.id = Table1.id
)
where exists (select 1 from Table2 t2 where t2.id = Table1.id);

请参阅演示。
结果：

| id    | col1  | col2   | col100 |
| ----- | ----- | ------ | ------ |
| 2346a | apple | green  | WA     |
| d27d7 | pear  | green  | VA     |
| 568ba | lemon | yellow | CA     |
| 9896f | plum  | purple | CA     |
| 1b7da | peach | pink   | GA     |