Les champs de mise à jour SQL d'une table à partir des champs d'une autre table
j'ai deux tables:
A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]
A
sera toujours un sous-ensemble de B
(ce qui signifie que toutes les colonnes de A
sont aussi dans B
).
je veux mettre à jour un enregistrement avec un ID
spécifique dans B
avec leurs données de A
pour toutes les colonnes de A
. Ce ID
existe à la fois dans A
et B
.
est-il une syntaxe UPDATE
ou n'importe quelle autre façon de le faire sans spécifier les noms de colonne, en disant simplement "mettre toutes les colonnes d'un" ?
J'utilise PostgreSQL, donc une commande spécifique non standard est aussi acceptée (cependant, pas préférable).
7 réponses
vous pouvez utiliser la clause non-standard de .
UPDATE b
SET column1 = a.column1,
column2 = a.column2,
column3 = a.column3
FROM a
WHERE a.id = b.id
AND b.id = 1
la question Est ancienne, mais j'ai senti que la meilleure réponse n'avait pas encore été donnée.
est une syntaxe de mise à jour ... sans préciser les noms de colonne ?
solution Générale avec le SQL dynamique
vous n'avez pas besoin de connaître le nom d'une colonne, à l'exception de quelques colonnes uniques à joindre sur ( id
dans exemple.) Fonctionne de manière fiable pour tous les cas possibles d'angle que je peux penser.
c'est spécifique à PostgreSQL. Je construis un code dynamique basé sur le information_schema , en particulier le tableau information_schema.columns
, qui est défini dans ANSI SQL et la plupart des RDBMS modernes (sauf pour Oracle) le soutiennent. Mais un DO
déclaration avec PL / pgSQL code d'exécution dynamique SQL est une syntaxe PostgreSQL totalement non standard.
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$;
en supposant une colonne correspondante dans b
pour chaque colonne dans a
, mais pas l'inverse. b
peut avoir des colonnes supplémentaires.
WHERE b.id = 123
est facultatif, ne mettre à jour qu'une ligne sélectionnée.
réponses connexes avec plus d'explications:
- la mise à jour dynamique échoue en raison de parenthèses indésirables autour de la chaîne dans plpgsql
- mise à Jour de plusieurs colonnes qui commencent avec une chaîne de caractères
solutions Partielles à la plaine SQL
avec liste de colonnes partagées
Vous avez encore besoin de connaître la liste des noms de colonnes qui les deux tables se partagent. Avec un raccourci de syntaxe pour mettre à jour plusieurs colonnes - plus court que ce que d'autres réponses ont suggéré jusqu'à présent dans tous les cas.
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;
cette syntaxe a été introduite avec Postgres 8.2 en décembre. 2006, bien avant que la question ne soit posée.
Plus de détails dans le manuel et cette réponse connexe sur dba.SE:
avec liste des colonnes B
si toutes les colonnes de A
sont définies NOT NULL
(mais pas nécessairement B
),
et vous savoir la colonne noms des B
(mais pas nécessairement 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;
le NATURAL LEFT JOIN
rejoint une ligne de b
où toutes les colonnes du même nom ont les mêmes valeurs. Nous n'avons pas besoin d'une mise à jour dans ce cas (rien ne change) et pouvons éliminer ces lignes au début du processus ( WHERE b.id IS NULL
).
Nous avons toujours besoin de trouver une ligne correspondante, donc b.id = ab.id
dans la requête externe.
il s'agit de la norme SQL à l'exception de la FROM
clause .
Il fonctionne peu importe laquelle des colonnes sont réellement présentes dans A
, mais la requête ne peut pas distinguer entre les valeurs nulles réelles et les colonnes manquantes dans A
, il est donc seulement fiable si toutes les colonnes dans A
sont définies NOT NULL
.
il y a plusieurs variantes possibles, selon ce que vous savoir au sujet des deux tables.
je travaille avec la base de données DB2 D'IBM depuis plus de dix ans et j'essaie maintenant d'apprendre PostgreSQL.
il fonctionne sur PostgreSQL 9.3.4, mais ne fonctionne pas sur DB2 10.5:
UPDATE B SET
COLUMN1 = A.COLUMN1,
COLUMN2 = A.COLUMN2,
COLUMN3 = A.COLUMN3
FROM A
WHERE A.ID = B.ID
Note: le problème principal provient d'une cause qui n'est pas supportée dans DB2 et aussi pas dans ANSI SQL.
il fonctionne sur DB2 10.5, mais ne fonctionne pas sur PostgreSQL 9.3.4:
UPDATE B SET
(COLUMN1, COLUMN2, COLUMN3) =
(SELECT COLUMN1, COLUMN2, COLUMN3 FROM A WHERE ID = B.ID)
enfin! Il fonctionne à la fois sur PostgreSQL 9.3.4 et 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)
C'est une grande aide. Le 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;
fonctionne parfaitement.
a noté que vous avez besoin d'un support ""
From "tbl_a" a
pour que ça marche.
pas nécessairement ce que vous avez demandé, mais peut-être utiliser l'héritage postgres pourrait aider?
CREATE TABLE A (
ID int,
column1 text,
column2 text,
column3 text
);
CREATE TABLE B (
column4 text
) INHERITS (A);
cela évite la nécessité de mettre à jour B.
Mais assurez-vous de lire tous les détails .
autrement, ce que vous demandez n'est pas considéré comme une bonne pratique-les choses dynamiques telles que les vues avec SELECT * ...
sont découragées( comme une telle légère commodité pourrait casser plus de choses que d'aider les choses), et ce vous demandez serait l'équivalent de la commande UPDATE ... SET
.
vous pouvez construire et exécuter dynamic sql pour ce faire, mais ce n'est vraiment pas l'idéal
Essayez De Suivre
Update A a, B b, SET a.column1=b.column1 where b.id=1
révisé: - mettre à jour plus d'une colonne
Update A a, B b, SET a.column1=b.column1, a.column2=b.column2 where b.id=1