在一个命令中有条件地更新表的多个列

I have a table that contains the columns name, client_name and requester_name. I need to update values of these columns from "Mic" to "Michael".

我有一个包含列名称,client_name和requester_name的表。我需要将这些列的值从“Mic”更新为“Michael”。

Here are some records that should be updated:

以下是一些应该更新的记录:

name  | client_name |  requester_name
------+-------------+----------------
Mic   | Jerry       | Jack
Jack  | Mic         | Mic
Jerry | Jack        | Mic

I tried the following query:

我尝试了以下查询:

UPDATE names
SET name='Michael', client_name='Michael', requester_name='Michael'
WHERE name='Mic' OR client_name='Mic' OR requester_name='Mic';

This query makes all columns change all names to 'Michael'.
What should the query look like to only apply changes where applicable?

此查询使所有列都将所有名称更改为“Michael”。查询应该如何仅在适用的情况下应用更改?

2 个解决方案

#1

It would be wise to add a WHERE clause.

添加WHERE子句是明智的。

UPDATE names
SET    name = CASE WHEN name = 'Mic' THEN 'Michael' ELSE name END
      ,client_name = CASE WHEN client_name = 'Mic' THEN 'Michael'
                     ELSE client_name END
      ,requester_name = CASE WHEN requester_name = 'Mic' THEN 'Michael'
                        ELSE requester_name END
WHERE 'Mic' IN (name, client_name, requester_name);

Else, the whole table will be updated unconditionally. Updates that change values to the same value are still updates creating dead rows, triggering triggers and so on. While the resulting rows would not be wrong, it would still bloat the table to twice its size, making VACUUM necessary, and be generally very slow.

否则,整个表格将无条件更新。将值更改为相同值的更新仍然是更新,从而创建死行,触发触发器等。虽然生成的行不会出错,但它仍会使表膨胀到其大小的两倍,这使得VACUUM成为必需,并且通常非常慢。

BTW, either form of the CASE statement is good here.

BTW,CASE声明的任何一种形式都很好。

#2

Not very elegant, not very efficient, but in one query:

不是很优雅,不是很有效,但在一个查询中:

UPDATE names SET 
name =  CASE  name 
    WHEN 'Mic' THEN 'Micheal' ELSE name END,
client_name = CASE  client_name 
    WHEN 'Mic' THEN 'Micheal' ELSE client_name END,
requester_name= CASE  requester_name 
    WHEN 'Mic' THEN 'Micheal' ELSE requester_name END ;

This uses the abbreviated ~~(Postgresql specific)~~ syntax of CASE.

这使用CASE的缩写(Postgresql特定)语法。

(BTW: I guess you meant 'Michael' instead of 'Micheal'?)

(顺便说一句:我猜你的意思是'迈克尔'而不是'迈克尔'?)

#1