SQL fields update one table from fields of another

Question

I have two tables:

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

A will always be a subset of B (which means that all columns of A also in B ).

I want to update a record with a specific ID in B with their data from A for all columns of A This ID exists in both A and B

Is there an UPDATE syntax or any other way to do this without specifying column names, just saying "set all columns A"?

I use PostgreSQL, so a certain non-standard command is also accepted (however, not recommended).

Answer 1

You can use non-standard FROM .

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

Answer 2

_{The question is old, but I felt that the best answer has not yet been given.}

Is there an UPDATE syntax ... without specifying column names ?

General solution with dynamic SQL

You do not need to know the column names, except for some unique columns to join ( id in the example). Works well for any possible corner event that I can think of.

This applies to PostgreSQL. I am creating dynamic code based on information_schema , in particular the information_schema.columns table, which is defined in ANSI SQL, and most modern RDBMS (except Oracle) support it. But DO with PL / pgSQL code that executes dynamic SQL is the completely 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 corresponding column in b for each column in a , but not vice versa. b may have additional columns.

WHERE b.id = 123 is optional to update only the selected row.

SQL Fiddle

Related answers with lots of explanation:

• Dynamic UPDATE update due to unwanted parentheses around a string in plpgsql
• Refresh multiple columns starting with a specific row

Partial Simple SQL Solutions

With a list of shared columns

You still need to know the list of column names that separate both tables. With a syntax shortcut to update multiple columns - shorter than the other answers offered so far anyway.

 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 Fiddle

This syntax was introduced with Postgres 8.2 in December 2006, long before the question was asked.
More details in the manual and this answer on dba.SE:

• Bulk update all columns

With a list of columns in b

If all columns are defined a NOT NULL (but not necessarily b ),
and you know the column names b (but not necessarily 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; 

NATURAL LEFT JOIN row from b , where all columns with the same name have the same value. We do not need an update in this case (nothing changes) and can eliminate these lines at the beginning of the process ( WHERE b.id IS NULL ).
We still need to find a matching string, so b.id = ab.id in the outer query.

SQL Fiddle

This is standard SQL except for the FROM . It works no matter which of the columns is actually present in a , but the query cannot distinguish between the actual NULL values ​​and the missing columns in a , so it is only reliable if all the columns are defined in a NOT NULL .

There are several possible options, depending on what you know about all the tables.

Answer 3

I have been working with an IBM DB2 database for over a decade and am 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. The main problem is the cause of FROM, which is not supported in DB2 and also not in ANSI SQL.

It works with 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) 

Answer 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 great.

noted that you need the bracket "" in

 From "tbl_a" a 

for it to work.

Answer 5

Not necessarily what you requested, but maybe using postgres inheritance could help?

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

This avoids the need for update B.

But be sure to read all the details .

Otherwise, what you ask is not considered good practice - dynamic things, such as representations with SELECT * ... , are discouraged (since such a little convenience can break more things than help anything), and that you ask, the UPDATE ... SET command will be equivalent.

Answer 6

you can create and execute dynamic sql for this, but its really not perfect

Answer 7

Try to follow

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

EDITED: - update more than one column

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