PostgreSQL - использование подзапроса для обновления значений нескольких столбцов

Ответ 1

Если вы хотите избежать двух подзапросов, запрос можно переписать следующим образом:

UPDATE table1
  SET col1 = o_min, col2 = o_max
FROM ( 
    SELECT min(ship_charge) as o_min, 
           max(ship_charge) as o_max
    FROM orders
) t 
WHERE col4 = 1001

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

Edit

Начиная с Postgres 9.5 это также можно записать как:

UPDATE table1
  SET (col1, col2) = (SELECT min(ship_charge), max(ship_charge) FROM orders)
WHERE col4 = 1001

Ответ 2

UPDATE table1
SET
    col1 = subquery.min_value,
    col2 = subquery.max_value
FROM
(

    SELECT
        1001 AS col4,
        MIN (ship_charge) AS min_value,
        MAX (ship_charge) AS max_value
    FROM orders
) AS subquery
WHERE table1.col4 = subquery.col4

Вы также можете возвратить несколько строк в подзапросе, если вы хотите обновить сразу несколько строк в таблице1.

Ответ 3

Это не самый эффективный способ сделать это, но это просто:

UPDATE table1 SET
col1 = (SELECT MIN (ship_charge) FROM orders),
col2 = (SELECT MAX (ship_charge) FROM orders)
WHERE col4 = 1001; 

Ответ 4

Один вариант (но не единственный) состоит в использовании двух отдельных подзапросов:

update table1
set col1 = (select min(ship_charge) from orders),
    col2 = (select max(ship_charge) from orders)
where col4 = 1001;

Из прекрасное руководство для PostgreSQL 9.0 UPDATE:

В соответствии со стандартом синтаксис списка столбцов должен позволять назначать список столбцов из одного выражения с строкой, например, подвыбор:

UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
 WHERE salesmen.id = accounts.sales_id);

В настоящее время это не реализовано - источник должен быть списком независимых выражений.

Ответ 5

Как говорится в официальном документе , вы можете использовать стандартное обновление Synopsis of PostgreSQL update
UPDATE table SET { column = { expression | DEFAULT } | ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...] [ FROM from_list ] [ WHERE condition ]

Итак, вы можете писать так:

UPDATE table1 SET TOTAL_MIN_RATE = subQuery."minRate", TOTAL_MAX_RATE = subQuery.maxRate FROM ( SELECT AVG (o.MIN_RATE) AS minRate, AVG (o.MAX_RATE) AS maxRate FROM ORDR o INNER JOIN table2 ba ON (o.PAY_ACCT_ID = ba.ACCT_ID) INNER JOIN table3 mb ON (ba.BANK_ID = mb.BANK_ID) WHERE ba.CNTRY_ID = table1.CNTRY_ID AND o.STUS_CD IN ( '01', '02', '03', '04', '05', '06' ) AND ( ( o.FRO_CRNCY_ID = table1.TO_CRNCY_ID AND o.TO_CRNCY_ID = table1.FRO_CRNCY_ID ) OR ( o.TO_CRNCY_ID = table1.TO_CRNCY_ID AND o.FRO_CRNCY_ID = table1.FRO_CRNCY_ID ) ) GROUP BY ba.CNTRY_ID ) subQuery;

Или более простой способ:

UPDATE table1 SET ( TOTAL_MIN_RATE, TOTAL_MAX_RATE ) = ( SELECT AVG (o.MIN_RATE) AS minRate, AVG (o.MAX_RATE) AS maxRate FROM ORDR o INNER JOIN table2 ba ON (o.PAY_ACCT_ID = ba.ACCT_ID) INNER JOIN table3 mb ON (ba.BANK_ID = mb.BANK_ID) WHERE ba.CNTRY_ID = table1.CNTRY_ID AND o.STUS_CD IN ( '01', '02', '03', '04', '05', '06' ) AND ( ( o.FRO_CRNCY_ID = table1.TO_CRNCY_ID AND o.TO_CRNCY_ID = table1.FRO_CRNCY_ID ) OR ( o.TO_CRNCY_ID = table1.TO_CRNCY_ID AND o.FRO_CRNCY_ID = table1.FRO_CRNCY_ID ) ) GROUP BY ba.CNTRY_ID );

Ответ 6

Использование UPDATE FROM является хорошим решением, если у вас нет простых подзапросов. В этом UPDATE я хотел установить event_profile_id таблицы photos как владелец (профили событий также являются владельцами) фотографии, на которую принадлежит фотография.

UPDATE photos
SET event_profile_id=photos_and_events.event_profile_id
FROM (
  SELECT
    ph.id photo_id,
    pr.id event_profile_id
  FROM photos ph, profiles pr, photo_sets ps
  WHERE ph.main_photo_set_id=ps.id AND ps.owner_profile_id=pr.id
) AS photos_and_events
WHERE photos.id=photos_and_events.photo_id;

Ответ 7

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

Следующий sql был протестирован на Postgresql 11, хотя он и должен нормально работать на v9+:

WITH permission_info AS (
    SELECT id
    FROM permission
    WHERE permission."key" LIKE 'prefix_for_admin_%'
), role_info AS (
    SELECT id 
    FROM role
    WHERE role."name" = 'Admin'
)
INSERT INTO role_permission_table
(
    role_id,
    permission_id
)
    SELECT role_info.id, permission_info.id FROM role_info, permission_info

ON CONFLICT DO NOTHING
;