CODEWITHSUNDEEP
sqlpostgresqlsql-updatedynamic-sql
Nir
Nir

Reputation: 2221

SQL update fields of one table from fields of another one

I have two tables:

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

A will always be subset of B (meaning all columns of A are 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 both in A and B.

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

Upvotes: 199

Views: 290085

Answers (8)

Benjamin Johnson
Benjamin Johnson

Reputation: 362

Not precisely the answer to the OP, but I found a solution in postgresql to my very similar problem, for anyone searching. I had a table with over a hundred columns and I needed to update one table from another with the same columns. Without much coding, in two passes I avoided lots of manual typing.

The first pass used string_agg function to generate my "set" portion of the update query.

SELECT string_agg('', '"' || column_name::text || '"=' || 's."' || column_name::text || '",')
  FROM information_schema.columns
 WHERE table_schema = 'public'
   AND table_name   = 'source_table'

I ran this and then pasted the single result into the update query (removing one trailing comma):

UPDATE target_table 
   SET [pasted_here]
  FROM source_table s
 WHERE target_table.id=s.id

Maybe this could be cleverly made into a single step, but it already solved my problem.

Upvotes: 0

Scott Bailey
Scott Bailey

Reputation: 8296

You can use the non-standard FROM clause.

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

Upvotes: 403

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656814

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.

db<>fiddle here
Old sqlfiddle

Related answers with more explanation:

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;

db<>fiddle here
Old sqlfiddle

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.

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.

Upvotes: 67

jochan
jochan

Reputation: 389

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)

Upvotes: 38

user2493970
user2493970

Reputation: 99

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.

Upvotes: 9

Salil
Salil

Reputation: 47482

Try Following

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

Upvotes: -5

Unreason
Unreason

Reputation: 12704

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.

Upvotes: 7

Daniel Brink
Daniel Brink

Reputation: 2504

you can build and execute dynamic sql to do this, but its really not ideal

Upvotes: 0

Related Questions