Обновите несколько строк в одном запросе, используя PostgreSQL

Question

Я ищу, чтобы обновить несколько строк в PostgreSQL в одном выражении. Есть ли способ сделать что-то вроде следующего?

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

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

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', 'O\'Connell'),
  (2, '[e-mail]', 'Robert', 'Duncan')
) as u2(id, email, first_name, last_name)
where u2.id = u.id;

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/

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 часть этого запроса

Answer 5

Допустим, у вас есть массив идентификаторов и эквивалентный массив статусов - вот пример, как это сделать с помощью статического 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;

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 такой же, как для report_ids, упомянутых выше. Приведенный выше запрос установит для 1 записи (той, которая соответствует условию) значение true, а для всех несоответствующих - значение false.

Answer 7

Ответ, предоставленный @zero323, отлично работает на Postgre 12. В случае, если у кого-то есть несколько значений для (упомянутых в вопросе 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 вопроса.

Answer 8

В дополнение к другим ответам, комментариям и документации можно использовать приведение типа данных. Это упрощает копирование:

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;

Answer 9

@Roman спасибо за решение, для всех, кто использует node, я сделал этот служебный метод, чтобы выкачать строку запроса для обновления n столбцов с n записями.

К сожалению, он обрабатывает только n записей с одинаковыми столбцами, поэтому параметр recordRows довольно строг.

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;