SQL update fields of one table from fields of another one.

Question

I'm having two tables:
A [ID, column1, column2, column3]
B [ID, column1, column2, column3, column4]

A table will always be subset of B table (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).

Thanks.

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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