SQL update fields of one table from fields of another one

Question

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

User · Answer

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

User · Answer

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  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  lt  gt   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 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 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 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 lt  gt 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] SQL update fields of one table from fields of another one

Examples related to sql

Examples related to postgresql

Examples related to sql-update

Examples related to dynamic-sql