Как сделать обновление + присоединиться к PostgreSQL?
В основном, я хочу сделать это:
update vehicles_vehicle v
join shipments_shipment s on v.shipment_id=s.id
set v.price=s.price_per_vehicle;
Я почти уверен, что это будет работать в MySQL (мой опыт), но, похоже, он не работает в postgres. Я получаю ошибку:
ERROR: syntax error at or near "join"
LINE 1: update vehicles_vehicle v join shipments_shipment s on v.shi...
^
Конечно, есть простой способ сделать это, но я не могу найти правильный синтаксис. Итак, как бы я написал это в PostgreSQL?
17 ответов
[WITH [RECURSIVE] with_query [,...]]
ОБНОВЛЕНИЕ [ТОЛЬКО] таблица [[AS] псевдоним]
SET {колонка = {выражение | ПО УМОЛЧАНИЮ} |
(столбец [, ...]) = ( {выражение | ПО УМОЛЧАНИЮ} [, ...]) } [, ...]
[ОТ from_list ]
[ГДЕ состояние | ГДЕ ТОКА ИЗ КУРСА
[ВОЗВРАЩЕНИЕ * | output_expression [ [ AS ] output_name ] [, ...] ]В вашем случае я думаю, что вы хотите этого:
UPDATE vehicles_vehicle AS v
SET price = s.price_per_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id
Ответ Марка Байерса является оптимальным в этой ситуации. Хотя в более сложных ситуациях вы можете взять запрос select, который возвращает rowids и вычисленные значения, и прикрепить его к запросу на обновление следующим образом:
with t as (
-- Any generic query which returns rowid and corresponding calculated values
select t1.id as rowid, f(t2, t2) as calculatedvalue
from table1 as t1
join table2 as t2 on t2.referenceid = t1.id
)
update t1
set value = t.calculatedvalue
from t
where id = t.rowid
Этот подход позволяет вам разработать и протестировать ваш запрос на выборку и в два шага преобразовать его в запрос на обновление.
Так что в вашем случае результат запроса будет:
with t as (
select v.id as rowid, s.price_per_vehicle as calculatedvalue
from vehicles_vehicle v
join shipments_shipment s on v.shipment_id = s.id
)
update vehicles_vehicle
set price = t.calculatedvalue
from t
where id = t.rowid
Обратите внимание, что псевдонимы столбцов являются обязательными, иначе PostgreSQL будет жаловаться на неоднозначность имен столбцов.
Позвольте мне объяснить немного больше на моем примере.
Задача: исправить информацию, когда абитуриенты (учащиеся, которые собираются покинуть среднюю школу) подали заявки в университет раньше, чем они получили школьные сертификаты (да, они получили сертификаты раньше, чем они были выданы (к указанной дате сертификата). Итак, мы будем увеличить дату подачи заявки, чтобы она соответствовала дате выдачи сертификата.
Таким образом. следующее MySQL-подобное утверждение:
UPDATE applications a
JOIN (
SELECT ap.id, ab.certificate_issued_at
FROM abiturients ab
JOIN applications ap
ON ab.id = ap.abiturient_id
WHERE ap.documents_taken_at::date < ab.certificate_issued_at
) b
ON a.id = b.id
SET a.documents_taken_at = b.certificate_issued_at;
Таким образом, становится PostgreSQL-подобным
UPDATE applications a
SET documents_taken_at = b.certificate_issued_at -- we can reference joined table here
FROM abiturients b -- joined table
WHERE
a.abiturient_id = b.id AND -- JOIN ON clause
a.documents_taken_at::date < b.certificate_issued_at -- Subquery WHERE
Как видите, оригинальный подзапрос JOIN"s ON пункт стал одним из WHERE условия, которые связаны с AND с другими, которые были перемещены из подзапроса без изменений. И больше нет необходимости JOIN таблица с самим собой (как это было в подзапросе).
Для тех, кто действительно хочет сделать JOIN Вы также можете использовать:
UPDATE a
SET price = b_alias.unit_price
FROM a AS a_alias
LEFT JOIN b AS b_alias ON a_alias.b_fk = b_alias.id
WHERE a_alias.unit_name LIKE 'some_value'
AND a.id = a_alias.id;
Вы можете использовать a_alias в SET раздел справа от знака равенства, если это необходимо. Поля слева от знака равенства не требуют ссылки на таблицу, так как они считаются исходной таблицей "а".
Для тех, кто хочет сделать JOIN, который обновляет ТОЛЬКО те строки, которые использует ваше соединение:
UPDATE a
SET price = b_alias.unit_price
FROM a AS a_alias
LEFT JOIN b AS b_alias ON a_alias.b_fk = b_alias.id
WHERE a_alias.unit_name LIKE 'some_value'
AND a.id = a_alias.id
--the below line is critical for updating ONLY joined rows
AND a.pk_id = a_alias.pk_id;
Это было упомянуто выше, но только через комментарий. Так как важно получить правильный результат, публикуя НОВЫЙ ответ, который работает
Вот так:
update vehicles_vehicle v
set price=s.price_per_vehicle
from shipments_shipment s
where v.shipment_id=s.id;
Просто, как я мог сделать это. Спасибо, парни!
Можно также сделать это:
update vehicles_vehicle
set price=s.price_per_vehicle
from vehicles_vehicle v
join shipments_shipment s on v.shipment_id=s.id;
Но затем у вас есть таблица транспортных средств там дважды, и вам разрешено использовать ее псевдоним только один раз, и вы не можете использовать псевдоним в части "set".
Чтобы добавить что-то очень важное ко всем приведенным выше замечательным ответам, когда вы хотите обновить
join-table, у вас могут возникнуть 2 проблемы:
- вы не можете использовать таблицу, которую хотите обновить, до другой
- Постгрес хочет
ONпункт послеJOINтак что вы не можете использовать толькоwhereстатьи.
Это означает, что в основном следующие запросы недействительны:
UPDATE join_a_b
SET count = 10
FROM a
JOIN b on b.id = join_a_b.b_id -- Not valid since join_a_b is used here
WHERE a.id = join_a_b.a_id
AND a.name = 'A'
AND b.name = 'B'
UPDATE join_a_b
SET count = 10
FROM a
JOIN b -- Not valid since there is no ON clause
WHERE a.id = join_a_b.a_id
AND b.id = join_a_b.b_id
a.name = 'A'
AND b.name = 'B'
Вместо этого вы должны использовать все таблицы в
FROM предложение вроде этого:
UPDATE join_a_b
SET count = 10
FROM a, b
WHERE a.id = join_a_b.a_id
AND b.id = join_a_b.b_id
AND a.name = 'A'
AND b.name = 'B'
Для некоторых это может быть просто, но я застрял на этой проблеме, задаваясь вопросом, что происходит, так что надеюсь, это поможет другим.
Ссылка ниже содержит пример, который решает и помогает лучше понять, как использовать update а также join с postgres.
UPDATE product
SET net_price = price - price * discount
FROM
product_segment
WHERE
product.segment_id = product_segment.id;
Смотрите: http://www.postgresqltutorial.com/postgresql-update-join/
Вот простой SQL, который обновляет Mid_Name в таблице Name3, используя поле Middle_Name из Name:
update name3
set mid_name = name.middle_name
from name
where name3.person_id = name.person_id;
Имя первой таблицы: tbl_table1 (tab1). Имя второй таблицы: tbl_table2 (tab2).
Установите столбец ac_status таблицы tbl_table1 на "НЕАКТИВНО"
update common.tbl_table1 as tab1
set ac_status= 'INACTIVE' --tbl_table1's "ac_status"
from common.tbl_table2 as tab2
where tab1.ref_id= '1111111'
and tab2.rel_type= 'CUSTOMER';
Чтобы ОБНОВИТЬ одну таблицу, используя другую, в PostGRE SQL/AWS (рабочее место SQL).
В PostGRE SQL вам нужно использовать объединения в запросе UPDATE следующим образом:
UPDATE TABLEA set COLUMN_FROM_TABLEA = COLUMN_FROM_TABLEB FROM TABLEA,TABLEB WHERE FILTER_FROM_TABLEA = FILTER_FROM_TABLEB;
Example:
Update Employees Set Date_Of_Exit = Exit_Date_Recorded , Exit_Flg = 1 From Employees, Employee_Exit_Clearance Where Emp_ID = Exit_Emp_ID
Таблица A — Столбцы сотрудников в таблице A — Date_Of_Exit, Emp_ID, Exit_Flg Таблица B — столбцы Employee_Exit_Clearance в таблице B — Exit_Date_Recorded, Exit_Emp_ID
Затронуто 1760 строк
Время выполнения: 29,18 с
РАБОТАЕТ ОТЛИЧНО!!!
POSTGRE SQL — ОБНОВЛЕНИЕ С ПРИСОЕДИНЕНИЕМ
НИЖЕ КОД. Проверьте расположение столбцов и идентификаторов, как показано ниже:
Если разместить именно так, как показано ниже, то работать будет только он!
---IF you want to update FIRST table
UPDATE table1
SET attribute1 = table2.attribute1
FROM table2
WHERE table2.product_ID = table1.product_ID;
ИЛИ
---IF you want to update SECOND table
UPDATE table2
SET attribute1 = table1.attribute1
FROM table1
WHERE table1.product_ID = table2.product_ID;
Следует проявлять осторожность, если соединение выполняется с неуникальным столбцом . Т.е. результат соединения дает больше значений, которые можно использовать при обновлении.
Некоторые RDMS вызывают исключение в этом случае, но PostgreSQL, очевидно, выполняет обновление с недетерминированным результатом.
Пример
Проверено на 14.1
create table tab as
select * from (values
(1,'a'),
(2,'b')
) t(id, att);
Мы используем CTE, гдеid = 1id дает два возможных значения для обновления. Используяorder byв CTE мы получаем разные результаты.
with t as (
select * from (values
(1,'c'),
(1,'d')
) t(id, att)
order by 2 /* Based on this order different update is performed */
)
update tab
set att = t.att
from t
where tab.id = t.id
В порядке возрастания столбец обновляется до значенияd(наивысшее значение)
id|att|
--+---+
1|d |
2|b |
при использовании убывающего порядка в CTE столбец обновляется до значенияc(наименьшее значение)
id|att|
--+---+
1|c |
2|b |
Мораль этой истории: всегда проверяйте, дает ли соединение уникальный результат.
--goal: обновить выбранные столбцы с помощью соединения (postgres)--
UPDATE table1 t1
SET column1 = 'data'
FROM table1
RIGHT JOIN table2
ON table2.id = table1.id
WHERE t1.id IN
(SELECT table2.id FROM table2 WHERE table2.column2 = 12345)
РЕДАКТИРОВАТЬ: не использовать, время выполнения увеличивается в квадрате
Жаль, что среда выполнения такая плохая, потому что синтаксис был очень элегантным. Я оставляю этот ответ, чтобы уберечь других от того, чтобы пойти по этому пути.
Этот ответ отличается от остальных тем, что вам не нужно повторять условие соединения.
- Вы присоединяетесь один раз в предложении FROM
- а предложение WHERE проверяет наличие строк из X в (X как X_joined join Y).
В результате это работает с естественными соединениями, что очень приятно.
Пример запроса
Скажи, что у тебя есть столshipmentкоторый вы хотите дополнить информацией из таблицыvehicle, и в обеих таблицах есть столбецvehicle_idтак что вы можете использоватьNATURAL JOIN.
---- DO NOT USE, quadratic runtime ----
EXPLAIN UPDATE shipment
SET shipment.speed = vehicle.average_speed
FROM
shipment s_joined NATURAL JOIN vehicle
WHERE
-- This is the magic condition
-- (EDIT: ... it probably causes the quadratic runtime, too)
shipment = s_joined
-- any further limitations go here:
AND shipment.destination = 'Perth'
Минимальный рабочий пример
-- A table with shipments, some with missing speeds
create temporary table shipment (
vehicle_id varchar(20),
cargo varchar(20),
speed integer
);
insert into shipment values
('cart', 'flowers', 60),
('boat', 'cabbage', null),
('cart', 'potatos', null),
('foot', 'carrots', null);
-- A table with vehicles whose average speed we know about
create temporary table vehicle (
vehicle_id varchar(20),
average_speed integer
);
insert into vehicle values
('cart', 6),
('foot', 5);
-- If the shipment has vehicle info, update its speed
---- DO NOT USE, quadratic runtime ----
UPDATE shipment
SET speed = vehicle.average_speed
FROM shipment as s_joined natural join vehicle
WHERE shipment = s_joined
AND shipment.speed is null;
-- After:
TABLE shipment;
┌────────────┬─────────┬───────┐
│ vehicle_id │ cargo │ speed │
├────────────┼─────────┼───────┤
│ cart │ flowers │ 60 │ <- not updated: speed was not null
│ boat │ cabbage │ │ <- not updated: no boat in join
│ cart │ potatos │ 6 │ <- updated
│ foot │ carrots │ 5 │ <- updated
└────────────┴─────────┴───────┘
Если у вас нет значения в одном столбце, а вместо этого вам нужно вычислить его из другой таблицы (в этом примереprice_per_vehicleот ). Тогда предполагая, чтоshipments_shipmentимеетpriceиvehicle_idстолбцы обновления для конкретного автомобиля могут выглядеть так:
-- Specific vehicle in this example is with id = 5
WITH prices AS (
SELECT SUM(COALESCE(s.price, 0)) AS price_per_vehicle
FROM shipments_shipment AS s
WHERE s.vehicle_id = 5
)
UPDATE vehicles_vehicle AS v
SET v.price = prices.price_per_vehicle
FROM prices
WHERE v.id = 5
Первый способ медленнее второго.
Первый:
DO $$
DECLARE
page int := 10000;
min_id bigint; max_id bigint;
BEGIN
SELECT max(id),min(id) INTO max_id,min_id FROM opportunities;
FOR j IN min_id..max_id BY page LOOP
UPDATE opportunities SET sec_type = 'Unsec'
FROM opportunities AS opp
INNER JOIN accounts AS acc
ON opp.account_id = acc.id
WHERE acc.borrower = true
AND opp.sec_type IS NULL
AND opp.id >= j AND opp.id < j+page;
COMMIT;
END LOOP;
END; $$;
Второй:
DO $$
DECLARE
page int := 10000;
min_id bigint; max_id bigint;
BEGIN
SELECT max(id),min(id) INTO max_id,min_id FROM opportunities;
FOR j IN min_id..max_id BY page LOOP
UPDATE opportunities AS opp
SET sec_type = 'Unsec'
FROM accounts AS acc
WHERE opp.account_id = acc.id
AND opp.sec_type IS NULL
AND acc.borrower = true
AND opp.id >= j AND opp.id < j+page;
COMMIT;
END LOOP;
END; $$;