Frage SQL aktualisiert Felder einer Tabelle von Feldern einer anderen Tabelle


Ich habe zwei Tabellen:

A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]

A wird immer Teilmenge von sein B (Bedeutet alle Spalten von A sind auch in B).

Ich möchte einen Datensatz mit einem bestimmten aktualisieren ID im B mit ihren Daten aus A für alle Spalten von A. Dies ID existiert beides in A und B.

Ist dort ein UPDATE Syntax oder jede andere Möglichkeit, dies zu tun, ohne die Spaltennamen zu spezifizieren, nur zu sagen "setze alle Spalten von A"?

Ich verwende PostgreSQL, daher wird auch ein bestimmter Nicht-Standard-Befehl akzeptiert (jedoch nicht bevorzugt).


75
2018-05-04 08:27


Ursprung


Antworten:


Sie können den Nicht-Standard verwenden VON Klausel.

UPDATE b
SET column1 = a.column1,
  column2 = a.column2,
  column3 = a.column3
FROM a
WHERE a.id = b.id
AND b.id = 1

157
2018-05-04 15:41



Die Frage ist alt, aber ich fühlte, dass die beste Antwort noch nicht gegeben wurde. 

Gibt es eine UPDATE-Syntax ... ohne Angabe der Spaltennamen?

Allgemeine Lösung mit dynamischem SQL

Sie müssen keine Spaltennamen kennen, mit Ausnahme einiger eindeutiger Spalten, denen Sie beitreten möchten (id im Beispiel). Funktioniert zuverlässig für jeden möglichen Eckfall, den ich mir vorstellen kann.

Dies ist spezifisch für PostgreSQL. Ich entwickle dynamischen Code basierend auf dem informationsschemainsbesondere die Tabelle information_schema.columns, die in ANSI SQL definiert ist und die meisten modernen RDBMS (außer Oracle) unterstützen. Aber a DO Aussage mit PL / pgSQL Code, der dynamisches SQL ausführt, ist keine PostgreSQL-Syntax.

DO
$do$
BEGIN

EXECUTE (
SELECT
'UPDATE b
 SET   (' || string_agg(quote_ident(column_name), ',') || ')
     = (' || string_agg('a.' || quote_ident(column_name), ',') || ')
 FROM   a
 WHERE  b.id = 123
 AND    a.id = b.id'
FROM   information_schema.columns
WHERE  table_name   = 'a'       -- table name, case sensitive
AND    table_schema = 'public'  -- schema name, case sensitive
AND    column_name <> 'id'      -- all columns except id
);

END
$do$;

Unter der Annahme einer passenden Spalte in b zum jeden Spalte in a, aber nicht umgekehrt. b kann zusätzliche Spalten haben.

WHERE b.id = 123 ist optional, um nur eine ausgewählte Zeile zu aktualisieren.

SQL-Geige.

Ähnliche Antworten mit mehr Erklärungen:

Teillösungen mit einfachem SQL

Mit Liste der geteilten Spalten

Sie müssen weiterhin die Liste der Spaltennamen kennen, die beide Tabellen gemeinsam haben. Mit einem Syntax-Shortcut zum Aktualisieren mehrerer Spalten - in jedem Fall kürzer als die bisher vorgeschlagenen Antworten.

UPDATE b
SET   (  column1,   column2,   column3)
    = (a.column1, a.column2, a.column3)
FROM   a
WHERE  b.id = 123    -- optional, to update only selected row
AND    a.id = b.id;

SQL-Geige.

Diese Syntax wurde im Dezember 2006 mit Postgres 8.2 eingeführt, lange bevor die Frage gestellt wurde.
Mehr Details in der Bedienungsanleitung und diese verwandte Antwort auf dba.SE:

Mit Liste der Spalten in B

Ob alle Spalten von A sind festgelegt NOT NULL (Aber nicht unbedingt B),
und Sie kennt die Spaltennamen von B (Aber nicht unbedingt A).

UPDATE b
SET   (column1, column2, column3, column4)
    = (COALESCE(ab.column1, b.column1)
     , COALESCE(ab.column2, b.column2)
     , COALESCE(ab.column3, b.column3)
     , COALESCE(ab.column4, b.column4)
      )
FROM (
   SELECT *
   FROM   a
   NATURAL LEFT JOIN  b -- append missing columns
   WHERE  b.id IS NULL  -- only if anything actually changes
   AND    a.id = 123    -- optional, to update only selected row
   ) ab
WHERE b.id = ab.id;

Das NATURAL LEFT JOIN schließt sich einer Reihe an b Alle Spalten mit demselben Namen haben dieselben Werte. Wir brauchen in diesem Fall keine Aktualisierung (nichts ändert sich) und können diese Zeilen frühzeitig im Prozess eliminieren (WHERE b.id IS NULL).
Wir müssen immer noch eine passende Zeile finden b.id = ab.id in der äußeren Abfrage.

SQL-Geige.

Dies ist Standard-SQL mit Ausnahme der FROM Klausel.
Es funktioniert unabhängig davon, welche der Spalten tatsächlich vorhanden sind A, aber die Abfrage kann nicht zwischen tatsächlichen Nullwerten und fehlenden Spalten unterscheiden A, so ist es nur zuverlässig, wenn alle Spalten in A sind festgelegt NOT NULL.

Es gibt mehrere mögliche Variationen, je nachdem, was Sie tun kennt über beide Tische.


30
2018-04-25 03:46



Ich arbeite seit mehr als zehn Jahren mit der IBM DB2-Datenbank und versuche nun, PostgreSQL zu lernen.

Es funktioniert mit PostgreSQL 9.3.4, funktioniert aber nicht mit DB2 10.5:

UPDATE B SET
     COLUMN1 = A.COLUMN1,
     COLUMN2 = A.COLUMN2,
     COLUMN3 = A.COLUMN3
FROM A
WHERE A.ID = B.ID

Anmerkung: Hauptproblem ist FROM cause, das in DB2 und auch nicht in ANSI SQL nicht unterstützt wird.

Es funktioniert mit DB2 10.5, funktioniert aber NICHT mit PostgreSQL 9.3.4:

UPDATE B SET
    (COLUMN1, COLUMN2, COLUMN3) =
               (SELECT COLUMN1, COLUMN2, COLUMN3 FROM A WHERE ID = B.ID)

ENDLICH! Es funktioniert sowohl mit PostgreSQL 9.3.4 als auch mit DB2 10.5:

UPDATE B SET
     COLUMN1 = (SELECT COLUMN1 FROM A WHERE ID = B.ID),
     COLUMN2 = (SELECT COLUMN2 FROM A WHERE ID = B.ID),
     COLUMN3 = (SELECT COLUMN3 FROM A WHERE ID = B.ID)

13
2017-11-26 12:41



Nicht unbedingt, was Sie gefragt haben, aber vielleicht hilft die Postgres-Vererbung?

CREATE TABLE A (
    ID            int,
    column1       text,
    column2       text,
    column3       text
);

CREATE TABLE B (
    column4       text
) INHERITS (A);

Dies vermeidet die Notwendigkeit, B zu aktualisieren.

Aber achten Sie darauf, alle zu lesen Einzelheiten.

Ansonsten wird das, wonach Sie fragen, nicht als eine gute Übung betrachtet - dynamische Dinge wie Ansichten mit SELECT * ... sind entmutigt (als solche leichte Bequemlichkeit könnte mehr Dinge brechen als Dinge zu helfen), und was Sie fragen, wäre gleichbedeutend für die UPDATE ... SET Befehl.


5
2018-05-04 08:48



Dies ist eine große Hilfe. Der Code

UPDATE tbl_b b
SET   (  column1,   column2,   column3)
    = (a.column1, a.column2, a.column3)
FROM   tbl_a a
WHERE  b.id = 1
AND    a.id = b.id;

funktioniert perfekt.

bemerkt, dass Sie eine Klammer "" in benötigen

From "tbl_a" a

damit es funktioniert.


5
2018-05-16 18:56



Sie können dynamische SQL erstellen und ausführen, um dies zu tun, aber es ist wirklich nicht ideal


1
2018-05-04 08:29



Versuchen Sie Folgendes

Update A a, B b, SET a.column1=b.column1 where b.id=1

EDITED: - Aktualisieren Sie mehr als eine Spalte

Update A a, B b, SET a.column1=b.column1, a.column2=b.column2 where b.id=1

-4
2018-05-04 08:33