Wie führt man ein Update + Join in PostgreSQL durch?

Question

Im Grunde genommen möchte ich Folgendes tun:

update vehicles_vehicle v 
    join shipments_shipment s on v.shipment_id=s.id 
set v.price=s.price_per_vehicle;

Ich bin mir ziemlich sicher, dass das in MySQL funktionieren würde (mein Hintergrund), aber es scheint in Postgres nicht zu funktionieren. Der Fehler, den ich bekomme, ist:

ERROR:  syntax error at or near "join"
LINE 1: update vehicles_vehicle v join shipments_shipment s on v.shi...
                                  ^

Sicherlich gibt es eine einfache Möglichkeit, dies zu tun, aber ich kann nicht die richtige Syntax finden. Also, wie würde ich dies in PostgreSQL schreiben?

Answer 1

Die UPDATE-Syntax ist:

\[ WITH \[ RECURSIVE \] with\_query \[, ...\] \]
UPDATE \[ ONLY \] table \[ \[ AS \] alias \]
    SET { column = { expression | DEFAULT } |
          ( column \[, ...\] ) = ( { expression | DEFAULT } \[, ...\] ) } \[, ...\]
    \[ FROM from\_list \]
    \[ WHERE condition | WHERE CURRENT OF cursor\_name \]
    \[ RETURNING \* | output\_expression \[ \[ AS \] output\_name \] \[, ...\] \]

In Ihrem Fall denke ich, dass Sie das wollen:

UPDATE vehicles_vehicle AS v 
SET price = s.price_per_vehicle
FROM shipments_shipment AS s
WHERE v.shipment_id = s.id

Answer 2

Die Antwort von Mark Byers ist in dieser Situation das Optimum. In komplexeren Situationen können Sie jedoch die Select-Abfrage, die rowids und berechnete Werte zurückgibt, wie folgt an die Aktualisierungsabfrage anhängen:

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 table1
set value = t.calculatedvalue
from t
where id = t.rowid

Mit diesem Ansatz können Sie Ihre Select-Abfrage entwickeln und testen und sie in zwei Schritten in eine Update-Abfrage umwandeln.

In Ihrem Fall lautet die Ergebnisabfrage also:

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

Beachten Sie, dass Spalten-Aliase obligatorisch sind, da sich PostgreSQL sonst über die Mehrdeutigkeit der Spaltennamen beschwert.

Answer 3

Lassen Sie mich das anhand eines Beispiels etwas näher erläutern.

Aufgabe: Korrigieren Sie die Information, dass Abiturienten sich früher an der Universität beworben haben, als sie ihre Zeugnisse bekommen haben (ja, sie haben ihre Zeugnisse früher bekommen, als sie ausgestellt wurden (zum angegebenen Zeugnisdatum). Wir werden also das Datum der Bewerbungseinreichung an das Ausstellungsdatum des Zeugnisses anpassen.

Daher die nächste MySQL-ähnliche Anweisung:

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;

Wird auf diese Weise PostgreSQL-ähnlich

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

Wie Sie sehen können, ist die ursprüngliche Subquery JOIN 's ON Klausel sind zu einer der WHERE Bedingungen, die durch AND mit anderen, die ohne Änderungen aus der Unterabfrage verschoben wurden. Und es besteht keine Notwendigkeit mehr für JOIN Tabelle mit sich selbst (wie in der Subquery).

Answer 4

Für diejenigen, die tatsächlich eine JOIN können Sie auch verwenden:

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;

Sie können den a_alias in der Datei SET auf der rechten Seite des Gleichheitszeichens, falls erforderlich. Für die Felder links des Gleichheitszeichens ist kein Tabellenbezug erforderlich, da davon ausgegangen wird, dass sie aus der ursprünglichen Tabelle "a" stammen.

Answer 5

Für diejenigen, die einen JOIN machen wollen, der NUR die Zeilen aktualisiert, die Ihr Join zurückgibt, verwenden Sie:

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;

Dies wurde oben erwähnt, aber nur durch einen Kommentar Da es entscheidend ist, um das richtige Ergebnis posten NEW Antwort, die funktioniert