Update a column of a table with a column of another table in PostgreSQL

by

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 your actual join condition.

I rewrote the UPDATE to join in table1 (with the FROM clause) instead of running correlated subqueries, because that is typically faster.
It also prevents that table2.val2 is 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 you would in a plain SELECT (tables, subqueries, set-returning functions, …). The manual:

from_item

A table expression allowing columns from other tables to appear in the WHERE condition and update expressions. This uses the same
syntax as the FROM clause of a SELECT statement; for example,
an alias for the table name can be specified. Do not repeat the target
table as a from_item unless you intend a self-join (in which
case it must appear with an alias in the from_item).

The final WHERE clause prevents updates that wouldn’t change anything – at 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

See:

More Related Contents:

  1. How do I (or can I) SELECT DISTINCT on multiple columns?
  2. updating table rows in postgres using subquery
  3. Return pre-UPDATE column values using SQL only
  4. Casting NULL type when updating multiple rows
  5. Slow simple update query on PostgreSQL database with 3 million rows
  6. SQL update fields of one table from fields of another one
  7. Bulk/batch update/upsert in PostgreSQL
  8. Postgres error updating column data
  9. Functions with variable number of input parameters
  10. Select first row in each GROUP BY group?
  11. SQL update from one Table to another based on a ID match
  12. How to use RETURNING with ON CONFLICT in PostgreSQL?
  13. IN vs ANY operator in PostgreSQL
  14. Escaping keyword-like column names in Postgres
  15. Delete duplicate rows (don’t delete all duplicate)
  16. GROUP BY + CASE statement
  17. Split column into multiple rows in Postgres
  18. Perform this hours of operation query in PostgreSQL
  19. Getting date list in a range in PostgreSQL
  20. Postgres Dynamic Query Function
  21. How do I delete a fixed number of rows with sorting in PostgreSQL?
  22. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  23. How to use an ALIAS in a PostgreSQL ORDER BY clause?
  24. Multiple array_agg() calls in a single query
  25. PostgreSQL does not accept column alias in WHERE clause
  26. Calculate working hours between 2 dates in PostgreSQL
  27. How to show row numbers in PostgreSQL query?
  28. Check whether string is a date Postgresql
  29. Counting the number of occurrences of a substring within a string in PostgreSQL
  30. Temporal database design, with a twist (live vs draft rows)

Leave a Comment