从联合选择语句更新表 [英] Update a table from a union select statement

问题描述

我有两个表如下:

表格

 k |  1    | 2 
--------------------
 a | mango | xx
 b | orange| xx
 c | xx    | apple
 d | xx    | banana
 a | xx    | mango

tableb

 k |  1    | 2 
--------------------
 a |       | 
 b |       |  
 c |       |  
 d |       | 

如何从 tablea 更新 tableb 以获得以下结果?

How can I update tableb from tablea so I get the results below?

tableb

 k |  1    | 2 
--------------------
 a | mango | mango
 b | orange| xx
 c | xx    | apple
 d | xx    | banana

如果我尝试使用如下更新语句

if in case I try to use a update statement like below

update tableb 
set 1 = x.1,
    2 = x.2
from 
 (
    select * from tablea
) x 
where tablea.k = x.k 

如果 k 重复，我可以让更新语句忽略 xx 吗?

Can I make the update statement to ignore xx if k is duplicate?

谢谢.

推荐答案

这里是 SELECT，希望你能做出更新.

Here is the SELECT, hope you can make the update.

1. 尝试搜索左侧名称为 <> 'xx' 的每个匹配项
2. 然后与我尚未使用的其余行合并.

SQL Fiddle 演示

SELECT t1."k", t1."1", COALESCE(t2."2", 'xx') "2"
FROM tablea t1
LEFT JOIN tablea t2
       ON  t1."1" = t2."2"
WHERE t1."1" <> 'xx'
UNION ALL
SELECT t1."k", t1."1", t1."2"
FROM tablea t1
WHERE t1."1" = 'xx'
  AND t1."2" NOT IN (SELECT t2."1" FROM tablea t2 WHERE t2."1" <> 'xx')

这篇关于从联合选择语句更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！