Обновление нескольких строк с разными первичными ключами в одном запросе в PostgreSQL?

Мне нужно обновить много столбцов во многих строках в PostgreSQL 9.1. В настоящее время я делаю это со многими разными запросами UPDATE, каждый из которых работает с другой строкой (на основе первичного ключа):

UPDATE mytable SET column_a = 12, column_b = 6 WHERE id = 1;
UPDATE mytable SET column_a = 1, column_b = 45 WHERE id = 2;
UPDATE mytable SET column_a = 56, column_b = 3 WHERE id = 3;

Мне нужно сделать несколько тысяч таких запросов.

Могу ли я в любом случае «массово обновить» множество строк в одном запросе в PostgreSQL? Если вы используете INSERT, вы можете вставить сразу несколько строк: (INSERT INTO mytable (column_a, column_b) VALUES ( (12, 6), (1, 45) );), есть что-то подобное для UPDATE?

Что-то вроде:

UPDATE mytable SET (id, column_a, column_b) FROM VALUES ( (1, 12, 6), (2, 1, 45), (3, 56, 3), … )

??

Важным моментом является то, что каждое «ЗНАЧЕНИЕ» будет обновлять только одну строку (на основе WHERE id =). Каждая строка будет иметь одинаковое фиксированное количество столбцов, которые необходимо обновить, но каждая строка будет иметь разные значения для каждого столбца, поэтому UPDATE mytable SET column_a = 12, column_b = 6 WHERE id IN (1, 2, 3); не будет работать.


postgresql sql sql-update postgresql-9.1 sql-optimization
person Rory    schedule 21.09.2013    source источник

Ответы (2)


arrow_upward
22
arrow_downward

Да, вы можете (и обычно это предпочтительнее в SQL) обновлять сразу несколько строк. Есть несколько способов сделать это, но наиболее читаемым и элегантным, на мой взгляд, является использование производной таблицы с идентификаторами и значениями:

update mytable as m set
    column_a = c.column_a,
    column_b = c.column_b
from (values
    (1, 12, 6),
    (2, 1, 45),
    (3, 56, 3)
) as c(id, column_a, column_b)
where c.id = m.id

Не очень читабельно, но более очевидным решением было бы использовать case:

update mytable set
    column_a = case id when 1 then 12 when 2 then 1 when 3 then 56 end,
    column_b = case id when 1 then 6 when 2 then 45 when 3 then 3 end
where id in (1, 2, 3)
person Roman Pekar    schedule 21.09.2013
comment
Этот подход отлично сработал для меня! Спасибо!! Также работает с объединением по нескольким ключам/столбцам. То есть, где c.id = m.id AND c.column_a=m.column_a; - person Michael Schreiber; 05.06.2015
comment
Я получаю «дублированное значение ключа нарушает уникальное ограничение». У меня есть составной уникальный индекс, и я пытаюсь обновить сразу несколько строк, чтобы прибегнуть к ним. - person Leeish; 09.03.2021

arrow_upward
8
arrow_downward

Если это применимо к вашему случаю, вы можете использовать его.

create table test(id int, a int, b int);

insert into test(id, a, b)
values
(1, 1, 1),
(2, 1, 1),
(3, 1, 1),
(4, 1, 1),
(5, 1, 1),
(6, 1, 1),
(7, 1, 1);


update test as d
set a = s.a, b = s.b
from 
(
  values
  (1, 2, 2),
  (2, 2, 2)
) as s(id, a, b)
where d.id = s.id

ДЕМО-ПРОГРАММА SQL FIDDLE

person Hamlet Hakobyan    schedule 21.09.2013