PostgreSQL:使用同一表中的某些条件，根据另一列值更改列值

Question

我有一个表，希望根据某些条件将列值替换为其他列值的值。

+---------------------+
| Cntry | Code | Value |    
+---------------------+
| US  | C11  | A     | 
| US  | C12  | B     |
| US  | C13  | C     |
| US  | C14  | D     |
| US  | C15  | E     |
| UK  | C11  | A     | 
| UK  | C12  | B     |
| UK  | C13  | C     |
| UK  | C14  | D     |
| UK  | C15  | E     |
+---------------------+

我想将基于Cntry 的C11的值替换为基于C14的值

所以我的输出应该是这样的。

+---------------------+
| Cntry | Code | Value |
+---------------------+
| US  | C11  | A     | 
| US  | C12  | B     |
| US  | C13  | C     |
| US  | C14  | A     |<====Repalce with C11 for US
| US  | C15  | E     |
| UK  | C11  | G     | 
| UK  | C12  | B     |
| UK  | C13  | C     |
| UK  | C14  | G     |<====Repalce with C11 for UK
| UK  | C15  | E     |
+---------------------+

在postgresql中有这样做的方法吗？

感谢

Answer 1

创建样本数据：

CREATE TABLE table1 (
cntry varchar NULL,
code varchar NULL,
value varchar NULL
);
INSERT INTO table1 (cntry, code, value) VALUES('US', 'C11', 'A');
INSERT INTO table1 (cntry, code, value) VALUES('US', 'C12', 'B');
INSERT INTO table1 (cntry, code, value) VALUES('US', 'C13', 'C');
INSERT INTO table1 (cntry, code, value) VALUES('US', 'C14', 'D');
INSERT INTO table1 (cntry, code, value) VALUES('US', 'C15', 'E');
INSERT INTO table1 (cntry, code, value) VALUES('UK', 'C11', 'G');
INSERT INTO table1 (cntry, code, value) VALUES('UK', 'C12', 'B');
INSERT INTO table1 (cntry, code, value) VALUES('UK', 'C13', 'C');
INSERT INTO table1 (cntry, code, value) VALUES('UK', 'C14', 'D');
INSERT INTO table1 (cntry, code, value) VALUES('UK', 'C15', 'E');

示例查询：

select 
t1.cntry, 
t1.code, 
case when t2.value is not null then t2.value else t1.value end as "value"
from table1 t1  
left join (
select 
cntry, 
'C14' as code, 
value 
from table1
where code = 'C11'
) t2 on t1.cntry = t2.cntry and t1.code = t2.code 
-- Result: 
cntry   code    value
US      C11     A
US      C12     B
US      C13     C
US      C14     A
US      C15     E
UK      C11     G
UK      C12     B
UK      C13     C
UK      C14     G
UK      C15     E

Answer 2

如果您想实际更改表的内容，那么UPDATE查询就可以了。

UPDATE mytable 
SET code = 'C11' 
WHERE code = 'C14'`

出于显而易见的原因，您应该非常小心地处理UPDATE查询。有几种方法可以避免我有时使用的错误：

1. 首先尝试SELECT语句来获取我认为要更改的行。如果这看起来不错，那么编辑查询以将SELECT更改为UPDATE
2. 复制这张表。请尝试对副本进行更新。如果您对结果满意，请尝试对原始表进行查询。使用SELECT INTO在副本上创建表(SELECT * INTO tablecopy FROM mytable(，然后使用DROP table(DROP tablecopy(