SQL update fields of one table from fields of another one
Solution 1
You can use the non-standard FROM clause.
UPDATE b
SET column1 = a.column1,
column2 = a.column2,
column3 = a.column3
FROM a
WHERE a.id = b.id
AND b.id = 1
Solution 2
The question is old but I felt the best answer hadn't been given, yet.
Is there an
UPDATE
syntax ... without specifying the column names?
General solution with dynamic SQL
Knowing only the primary key column of both tables
You don't need to know any column names except for some unique column(s) to join on (id
in the example). Works reliably for any possible corner case I can think of.
This is specific to PostgreSQL. I am building dynamic code based on the the information_schema, in particular the table information_schema.columns
, which is defined in the SQL standard and most major RDBMS (except Oracle) have it. But a DO
statement with PL/pgSQL code executing dynamic SQL is totally non-standard 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$;
Assuming a matching column in b
for every column in a
, but not the other way round. b
can have additional columns.
WHERE b.id = 123
is optional, to update a selected row.
Related answers with more explanation:
- Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql
- Update multiple columns that start with a specific string
Partial solutions with plain SQL
Knowing the list of shared columns
You know the list of column names that both tables share. With a syntax shortcut for updating multiple columns - shorter than what other answers suggested so far in any case.
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;
This syntax was introduced with Postgres 8.2 in 2006, long before the question was asked. Details in the manual.
Related:
Knowing list of columns in B
If all columns of A
are defined NOT NULL
(but not necessarily all columns of B
),
and you know the column names of B
(but not necessarily those of 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;
The NATURAL LEFT JOIN
joins a row from b
where all columns of the same name hold same values. We don't need an update in this case (nothing changes) and can eliminate those rows early in the process (WHERE b.id IS NULL
).
We still need to find a matching row, so b.id = ab.id
in the outer query.
This is standard SQL except for the FROM
clause.
It works no matter which of the columns are actually present in A
, but the query cannot distinguish between actual NULL values and missing columns in A
, so it is only reliable if all columns in A
are defined NOT NULL
.
There are multiple possible variations, depending on what you know about both tables.
Solution 3
I have been working with IBM DB2 database for more then decade and now trying to learn PostgreSQL.
It works on PostgreSQL 9.3.4, but does not work on DB2 10.5:
UPDATE B SET
COLUMN1 = A.COLUMN1,
COLUMN2 = A.COLUMN2,
COLUMN3 = A.COLUMN3
FROM A
WHERE A.ID = B.ID
Note: Main problem is FROM cause that is not supported in DB2 and also not in ANSI SQL.
It works on DB2 10.5, but does NOT work on PostgreSQL 9.3.4:
UPDATE B SET
(COLUMN1, COLUMN2, COLUMN3) =
(SELECT COLUMN1, COLUMN2, COLUMN3 FROM A WHERE ID = B.ID)
FINALLY! It works on both PostgreSQL 9.3.4 and 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)
Solution 4
This is a great help. The 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;
works perfectly.
noted that you need a bracket "" in
From "tbl_a" a
to make it work.
Solution 5
Not necessarily what you asked, but maybe using postgres inheritance might help?
CREATE TABLE A (
ID int,
column1 text,
column2 text,
column3 text
);
CREATE TABLE B (
column4 text
) INHERITS (A);
This avoids the need to update B.
But be sure to read all the details.
Otherwise, what you ask for is not considered a good practice - dynamic stuff such as views with SELECT * ...
are discouraged (as such slight convenience might break more things than help things), and what you ask for would be equivalent for the UPDATE ... SET
command.
Related videos on Youtube
Nir
Updated on December 18, 2021Comments
-
Nir over 2 years
I have two tables:
A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4]
A
will always be subset ofB
(meaning all columns ofA
are also inB
).I want to update a record with a specific
ID
inB
with their data fromA
for all columns ofA
. ThisID
exists both inA
andB
.Is there an
UPDATE
syntax or any other way to do that without specifying the column names, just saying "set all columns of A"?I'm using PostgreSQL, so a specific non-standard command is also accepted (however, not preferred).
-
zubair-0 about 6 yearsI think this is what you wanna do, dba.stackexchange.com/a/58383
-
-
Nir about 14 yearsI don't understand how it copies column1, column2 and column3. And I do need to explicit mention column1.
-
Nir about 14 yearsI thought about that. I thought I could make my query compliant with later changes to both tables, but dynamic sql seems to be too complicated than just specify all fields and forget about forward compatibility.
-
CyberSrikanth about 14 yearsyes, it will be complicated, but should be forward compatible with later columns being added or removed. You'll have to first do a query to get the column names from both tables, then match the column names and then write the dynamic sql to do the update based on the matching column names. a fun project actually :)
-
Nir about 14 yearsI not sure how inheritance will solve this. Do you mean adding an update trigger for A that also updates B? I don't want to synchronize A with B all the time, only upon request. And in such case, I can't use the triggers.
-
Unreason about 14 yearsYes, if it is only in certain cases then inheritance would not work and in that case I advise against dynamic query approach. (still there are ways to achieve this using postgres procedural languages. also if you want to use triggers you can use them as well - by adding sync field for example firing trigger only when it is set).
-
cluesque about 12 yearsThe question is asking about how to do it without specifying all the column names. (And I am too.)
-
melbic about 10 yearsDoesn't work for me. I get the following error: ERROR: syntax error at or near ","
-
Erwin Brandstetter over 9 yearsThis non-standard syntax would work for
UPDATE
in MySQL, but it's invalid for PostgreSQL. -
Erwin Brandstetter over 9 yearsNote that the second and third queries are not completely equivalent to the the first. If no matching row is found in
B
, the first statement does nothing (original row remains untouched), while the other two overwrite columns with NULL values. -
Victoria Stuart about 6 yearsI agree with @cluesque, but this answer is an excellent way to use values in one column in a table as a lookup table for replacing values in a column in another table (see SO 21657475), so +1 ...
-
Edgar Ortega almost 6 yearsThe power of SQL! Just noticed when you add parenthesis in the set clause (
SET (column1) = (a.column)
) Postgres will treat it as a another kind of update and give and error like this:source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression
-
YasirAzgar almost 6 yearsWhy b.id = 1 is needed ?
-
Scott Bailey over 5 years@YasirAzgar the b.id = 1 is to limit what rows in b get updated. Otherwise we would update every row in the table. Occasionally, that might be what you want. But the original question was to update a specific row in b.
-
muad-dweeb over 4 yearsThis is what I needed for my particular problem: updating one table's column with values from another table's differently-named column.