SQL update fields of one table from fields of another one

sql postgresql sql-update dynamic-sql
Nir picture Nir · May 4, 2010 · Viewed 159.4k times · Source

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

Answer

Scott Bailey picture Scott Bailey · May 4, 2010

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

Related questions

How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
updating table rows in postgres using subquery

Using postgres 8.4, My goal is to update existing table: CREATE TABLE public.dummy ( address_id SERIAL, addr1 character(40), addr2 character(40), city character(25), state character(2), zip character(5), customer boolean, supplier boolean, partner boolean ) WITH ( OIDS=FALSE ); Initially i tested my query …

Read More
Update or Insert (multiple rows and columns) from subquery in PostgreSQL

I'm trying to do something like this in postgres: UPDATE table1 SET (col1, col2) = (SELECT col2, col3 FROM othertable WHERE othertable.col1 = 123); INSERT INTO table1 (col1, col2) VALUES (SELECT col1, col2 FROM othertable) But point 1 is not possible even with …

Read More