使用PostgreSQL更新多行的同一查询

Question

我想在PostgreSQL中用一条语句更新多行，是否有类似以下的方法可以实现？

UPDATE table 
SET 
 column_a = 1 where column_b = '123',
 column_a = 2 where column_b = '345'

- newUserNameHere

---

我一直在那个页面上尝试找到它，但是我找不到。我看到你可以使用一个where语句更新多行，但我不知道如何更新每行都有自己的where语句的多行。我还在谷歌上搜索了一下，没有找到真正清晰的答案，所以我希望有人能提供一个明确的例子。 - newUserNameHere

Answer 1

您也可以使用update ... from语法，并使用映射表。如果要更新多个列，则更具通用性：

update test as t set
    column_a = c.column_a
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where c.column_b = t.column_b;

您可以添加任意数量的列：

update test as t set
    column_a = c.column_a,
    column_c = c.column_c
from (values
    ('123', 1, '---'),
    ('345', 2, '+++')  
) as c(column_b, column_a, column_c) 
where c.column_b = t.column_b;

sql fiddle demo

- Roman Pekar

---

同样，可能需要指定正确的数据类型。以日期为例：... from (values ('2014-07-21'::timestamp, 1), ('2014-07-20', 2), ... 更多详细信息请参见PostgreSQL文档。 - José Andias

非常好，感谢您的澄清！Postgres文档有点令人困惑。 - skwidbreth

太酷了！没想到今天会学到一个新的SQL结构。 - WestCoastProjects

请注意，这是我正在寻找的最接近的版本。我们可以动态地向数组添加行：SELECT s.id, s.marked_for_deletion, s.name FROM unnest( array[ ('2f888809-2777-524b-abb7-13df413440f5',true,'沙拉叉'), ('f2924dda-8e63-264b-be55-2f366d9c3caa',false,'瓜球勺'), ('d9ecd18d-34fd-5548-90ea-0183a72de849',true,'火锅叉') ]::item[] ) s; ; https://stackoverflow.com/questions/57517980/improving-a-function-that-upserts-based-on-an-input-array - Charlie 木匠

使用 c 的主要原因是什么？ - ambigus9

Answer 2

根据@Roman的解决方案，您可以设置多个值：

update users as u set -- postgres FTW
  email = u2.email,
  first_name = u2.first_name,
  last_name = u2.last_name
from (values
  (1, '[e-mail]', 'Hollis', 'Connell'),
  (2, '[e-mail]', 'Robert', 'Duncan')
) as u2(id, email, first_name, last_name)
where u2.id = u.id;

- Benjamin Crouzier

---

这似乎是他的解决方案... UPDATE FROM (VALUES...) WHERE。它只基于什么？ - Evan Carroll

我更喜欢这个答案，因为变量名使得理解正在发生的事情更容易。 - Sky

哇，非常精确明了。我正在尝试在GoLang中实现类似的东西。那么我可以传递一个结构体数组作为参数吗？就像这样：from (values $1)，其中$1是一个结构体数组。在上面的例子中，该结构体将具有id、first_name和last_name属性。 - Reshma Suresh

Answer 3

可以的：

UPDATE foobar SET column_a = CASE
   WHEN column_b = '123' THEN 1
   WHEN column_b = '345' THEN 2
END
WHERE column_b IN ('123','345')

工作证明: http://sqlfiddle.com/#!2/97c7ea/1

- zero323

---

如果在最后一个“WHEN？THEN？”行之后添加“ELSE column_b”，则该列将设置为其当前值，从而防止MatheusQI所说的情况发生。 - Kevin Orriss

这不是他要求的。他需要更新多列，而不是根据列B设置列A。 - Alkanshel

这不正是OP所要求的吗 - 只需要更新column_a（基于column_b的值），而不是多个列，对吧？ - kevlarr

非常适用于Postgre 12。谢谢！ - Binita Bharati

Answer 4

如果您想在单个查询中更新多行，请尝试以下方法：

UPDATE table_name
SET 
column_1 = CASE WHEN any_column = value and any_column = value THEN column_1_value end,
column_2 = CASE WHEN any_column = value and any_column = value THEN column_2_value end,
column_3 = CASE WHEN any_column = value and any_column = value THEN column_3_value end,
.
.
.
column_n = CASE WHEN any_column = value and any_column = value THEN column_n_value end

如果您不需要额外的条件，请删除此查询中的and部分。

- Omar

Answer 5

假设你有一个ID数组和一个等价的状态数组 - 下面是一个使用静态SQL（不因不同值而改变的SQL查询）对这些数组进行操作的示例：

drop table if exists results_dummy;
create table results_dummy (id int, status text, created_at timestamp default now(), updated_at timestamp default now());
-- populate table with dummy rows
insert into results_dummy
(id, status)
select unnest(array[1,2,3,4,5]::int[]) as id, unnest(array['a','b','c','d','e']::text[]) as status;

select * from results_dummy;

-- THE update of multiple rows with/by different values
update results_dummy as rd
set    status=new.status, updated_at=now()
from (select unnest(array[1,2,5]::int[]) as id,unnest(array['a`','b`','e`']::text[]) as status) as new
where rd.id=new.id;

select * from results_dummy;

-- in code using **IDs** as first bind variable and **statuses** as the second bind variable:
update results_dummy as rd
set    status=new.status, updated_at=now()
from (select unnest(:1::int[]) as id,unnest(:2::text[]) as status) as new
where rd.id=new.id;

- Tal Barda

Answer 6

我曾遇到类似的情况，CASE表达式对我非常有帮助。

UPDATE reports SET is_default = 
case 
 when report_id = 123 then true
 when report_id != 123 then false
end
WHERE account_id = 321;

报告 - 这里是一个表格，account_id对于上述报告ID是相同的。以上查询将把一个记录（与条件匹配的记录）设置为true，而所有不匹配的记录都设置为false。

- Ricky Boy

Answer 7

@zero323提供的答案在Postgre 12上运行良好。如果有人对column_b（在OP的问题中提到）有多个值

UPDATE conupdate SET orientation_status = CASE
   when id in (66934, 39) then 66
   when id in (66938, 49) then 77
END
WHERE id IN (66934, 39, 66938, 49)

在上面的查询中，id类似于column_b；orientation_status类似于问题的column_a。

- Binita Bharati

Answer 8

@Roman 谢谢你提供的解决方案，对于使用node的任何人，我制作了这个实用方法来生成查询字符串以更新n列和n条记录。

不幸的是，它只处理具有相同列的n条记录，因此recordRows参数非常严格。

const payload = {
  rows: [
    {
        id: 1,
        ext_id: 3
    },
    {
        id: 2,
        ext_id: 3
    },
    {
        id: 3,
        ext_id: 3
    } ,
        {
        id: 4,
        ext_id: 3
    } 
  ]
};

var result = updateMultiple('t', payload);

console.log(result);
/*
qstring returned is:

UPDATE t AS t SET id = c.id, ext_id = c.ext_id FROM (VALUES (1,3),(2,3),(3,3),(4,3)) AS c(id,ext_id) WHERE c.id = t.id
*/



function updateMultiple(table, recordRows){
  var valueSets = new Array();
  var cSet = new Set();
  var columns = new Array();
  for (const [key, value] of Object.entries(recordRows.rows)) {
    var groupArray = new Array();
    for ( const [key2, value2] of Object.entries(recordRows.rows[key])){    
      if(!cSet.has(key2)){
        cSet.add(`${key2}`);
        columns.push(key2);
      }
      groupArray.push(`${value2}`); 
    }
    valueSets.push(`(${groupArray.toString()})`);
  }
  var valueSetsString = valueSets.join();  
  var setMappings = new String();
  for(var i = 0; i < columns.length; i++){
    var fieldSet = columns[i];
    
      setMappings += `${fieldSet} = c.${fieldSet}`;
      if(i < columns.length -1){
        setMappings += ', ';
      }
  }
  var qstring = `UPDATE ${table} AS t SET ${setMappings} FROM (VALUES ${valueSetsString}) AS c(${columns}) WHERE c.id = t.id`;
  return qstring;
}

- rjdb123

Answer 9

除了其他答案、评论和文档，数据类型转换可以放置在使用上。这样可以更容易地复制粘贴。

update test as t set
    column_a = c.column_a::number
from (values
    ('123', 1),
    ('345', 2)  
) as c(column_b, column_a) 
where t.column_b = c.column_b::text;

- Anton Bessonov

Answer 10

我认为被接受的答案并不完全正确。它是有序依赖的。以下是一个示例，如果按照答案的方法将无法正确运行。

create table xxx (
    id varchar(64),
    is_enabled boolean
);

insert into xxx (id, is_enabled) values ('1',true);
insert into xxx (id, is_enabled) values ('2',true);
insert into xxx (id, is_enabled) values ('3',true);

UPDATE public.xxx AS pns
        SET is_enabled         = u.is_enabled
            FROM (
            VALUES
         (
            '3',
            false
         ,
            '1',
            true
         ,
            '2',
            false
         )
        ) AS u(id, is_enabled)
            WHERE u.id = pns.id;

select * from xxx;

所以问题仍然存在，有没有一种无序的方法来做到这一点？

----尝试了几件事情后，这似乎是无序的。

UPDATE public.xxx AS pns
        SET is_enabled         = u.is_enabled
            FROM (
            SELECT '3' as id, false as is_enabled UNION
            SELECT '1' as id, true as is_enabled UNION
            SELECT '2' as id, false as is_enabled
         ) as u
            WHERE u.id = pns.id;

- Peter Litvak

---

我猜测你的查询语句中VALUES部分的括号有误。正确的写法应该是： ('2', false), ('1', true), ('3', false)这样就可以正常工作了。 - Yozi