SQL update fields of one table from fields of another one

You can use the non-standard FROM clause.

UPDATE bSET column1 = a.column1,  column2 = a.column2,  column3 = a.column3FROM aWHERE a.id = b.idAND b.id = 1

_{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

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$BEGINEXECUTE (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.columnsWHERE  table_name   = 'a'       -- table name, case sensitiveAND    table_schema = 'public'  -- schema name, case sensitiveAND    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.

SQL Fiddle.

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

With list of shared columns

You still need to 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 bSET   (  column1,   column2,   column3)    = (a.column1, a.column2, a.column3)FROM   aWHERE  b.id = 123    -- optional, to update only selected rowAND    a.id = b.id;

SQL Fiddle.

This syntax was introduced with Postgres 8.2 in 2006, long before the question was asked. Details in the manual.

Related:

• Bulk update of all columns

With list of columns in B

If all columns of A are defined NOT NULL (but not necessarily B),
and you know the column names of B (but not necessarily A).

UPDATE bSET   (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   ) abWHERE 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.

db<>fiddle here
Old sqlfiddle.

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.

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.COLUMN3FROM AWHERE 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)