Double join in update query?

Question

[Артем]

Здравствуйте. Есть две таблицы - note, contractor.
В таблице note лежат два ключа на таблицу contractor (fk_contractor_from, fk_contractor_to);
Нужно произвести update note по условиям из contractor.
Как быть?

UPDATE 
    delivery_note 
SET 
    fk_contractor_from = fk_contractor_to,
    fk_contractor_to = fk_contractor_from 
WHERE 
    fk_contractor_from IN(
        SELECT DISTINCT id_contractor FROM contractor WHERE "type" = 'WH'
    )
    AND
    fk_contractor_to IN (
        SELECT DISTINCT id_contractor FROM contractor WHERE "type" = 'DC'
    )

Нужно уйти от вложенных запросов, что-то вроде такого, только работающее:

UPDATE 
    delivery_note as dn
SET 
    dn.fk_contractor_from = dn.fk_contractor_to,
    dn.fk_contractor_to = dn.fk_contractor_from
JOIN contractor as c_from ON dn.fk_contractor_from = c_from.id_contractor
JOIN contractor as c_to ON dn.fk_contractor_to = c_to.id_contractor
WHERE
    c_from.type = 'WH'
    AND
    c_to.type = 'DC'

Comments

[mletov]

А чем второй запрос плох?
Только я бы условия из WHERE перенес в ON

[Артем]

mletov, плох тем, что он не соответствует синтаксису и не работает. а ON - это условия джоинов, они на своем месте.

[mletov]

Артем, Синтаксису не соответствует т к вместо первого JOIN нужен FROM

Answer 1

[Артем]

UPDATE 
    delivery_note as dn
SET 
    fk_contractor_from = fk_contractor_to,
    fk_contractor_to = fk_contractor_from
FROM contractor c_from, contractor c_to
WHERE
    (c_from.type = 'WH' AND dn.fk_contractor_from = c_from.id_contractor)
    AND
    (c_to.type = 'DC' AND dn.fk_contractor_to = c_to.id_contractor)

Answer 2

[Максим Федоров]

Нашел такой пример:

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

Comments

[Артем]

видел. Мне по факту нужны именно два джоина одной таблицы, поскольку перечисление внутри FROM фактически, как я понял из доков, является cross join'ом. Время выполнения запроса это подтверждает.

[Максим Федоров]

Посмотри в сторону from_list из доки https://www.postgresql.org/docs/10/static/sql-upda...

from_list
A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement. Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).

Answer 3

[d-stream]

По-моему в pg как и в ms синтаксис апдейта с джойнами выглядит так:

update tablex
set ....
from tablex
join ..
join ..
where ..