Update a column of a table with a column of anothe

I want to copy all the values from one column val1 of a table table1 to one column val2 of another table table2. I tried this command in PostgreSQL:

update table2
set val2 = (select val1 from table1)

But I got this error:

ERROR: more than one row returned by a subquery used as an expression

Is there an alternative to do that?

回答1:

Your UPDATE query should really look like this:

UPDATE table2 t2
SET    val2 = t1.val1
FROM   table1 t1
WHERE  t2.table2_id = t1.table2_id
AND    t2.val2 IS DISTINCT FROM t1.val1  -- optional, to avoid empty updates

The way you had it, there was no link between individual rows of the two tables. Every row would be fetched from table1 for every row in table2. This made no sense (in an expensive way) and also triggered the syntax error, because a subquery expression in this place is only allowed to return a single value.

I fixed this by joining the two tables on table2_id. Replace that with whatever actually links the two.
I rewrote the UPDATE to join in table1 (with the FROM clause) instead of running correlated subqueries, because that is regularly faster by an order of magnitude.
It also prevents that table2.val2 would be nullified where no matching row is found in table1. Instead, nothing happens to such rows with this form of the query.
You can include all the same things in the FROM list you could include in a plain SELECT (like multiple tables or subqueries). Per documentation:

from_list
A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement. Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).

The final WHERE clause prevents updates that wouldn't change anything - which is practically always a good idea (full cost, but no gain - exotic exceptions apply).

回答2:

update table1 set table1_column= table2.column from table2 table2 where table1_id= table2.id