Menu
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?
471
UPDATE table SET col = new_value WHERE col = old_value AND other_col = some_other_value; UPDATE table SET col = new_value WHERE col = old_value OR other_col = some_other_value; As you can see, you can expand the WHERE clause as much as you'd like in order to filter down the rows for updating to what you need.
First, specify the name of the table that you want to update data after the UPDATE keyword. Second, specify columns and their new values after SET keyword. The columns that do not appear in the SET clause retain their original values. Third, determine which rows to update in the condition of the WHERE clause.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
Your UPDATE query should 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, see below 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 typically 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 add table expressions to the FROM list like would in a plain SELECT (tables, subqueries, set-returning functions, ...). The manual:
from_listA 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 theFROMClause of aSELECTstatement. Note that the target table must not appear in thefrom_list, unless you intend a self-join (in which case it must appear with an alias in thefrom_list).
The final WHERE clause prevents updates that wouldn't change anything - which is practically always a good idea (almost full cost but no gain, exotic exceptions apply). If both old and new value are guaranteed to be NOT NULL, simplify to:
AND t2.val2 <> t1.val1 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
