I want to copy all the values from one column
set val2 = (select val1 from table1)
ERROR: more than one row returned by a subquery used as an expression
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:
A list of table expressions, allowing columns from other tables to appear in the
WHEREcondition and the update expressions. This is similar to the list of tables that can be specified in the
FROMClause of a
SELECTstatement. 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).
WHEREclause prevents updates that wouldn't change anything - which is practically always a good idea (full cost, but no gain - exotic exceptions apply).