Update a Column of a Table with a Column of Another Table in Postgresql

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

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_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 (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

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

postgresql update a table column based on values stored in another table

You are comparing string constants in your WHERE clause, not columns. So your where clause:

WHERE '2018_01.aisshiptype' = 'shiptype_emodnet.aisshiptype';

is always false, because the string literal '2018_01.aisshiptype' is never the same as the string literal 'shiptype_emodnet.aisshiptype'. So your where condition is essentially the same as:

where false

---

Identifiers need to be quoted with double quotes ("). Single quotes (') are only for string literals.

UPDATE "2018_01"
  SET emodnet_code = shiptype_emodnet.emodnet_type
FROM "shiptype_emodnet" 
WHERE "2018_01".aisshiptype = shiptype_emodnet.aisshiptype;

And you only need the double quotes for columns or tables that use names that are illegal in SQL or were created using double quotes and mixed case.

Updating a column by counting data from another table

Use the proprietary UPDATE ... FROM to perform a join that can be something else than a nested loop:

UPDATE tableA SET tableA.column1 = tbc.count
FROM (SELECT column2,
             count(*) AS count
      FROM tableB
      GROUP BY column2) AS tbc
WHERE tableA.column2 = tbc.column2;

Postgres - Move column to another table or Update another column

This needs an UPDATE:

update PRODUCT_IMAGE pi
set is_primary = True
from PRODUCT p
where p.id = pi.product_id and p.image_url = pi.url;

and then INSERT:

insert into PRODUCT_IMAGE (product_id, url, is_primary)
select p.id, p.image_url, False
from PRODUCT p
where not exists (
  select 1 from PRODUCT_IMAGE
  where product_id = p.id and url = p.image_url
);

See the demo.

updating column with value from another column in postgres table

An update with basic concatenation should work here:

UPDATE yourTable
SET email = username || '_' || email;

Update column based on select from another table

You can join an UPDATE statement with a FROM clause. In your case:

UPDATE names
SET min_price = q.min_price
FROM (
  SELECT name, MIN(price) as min_price
  FROM historical
  GROUP BY name
) as q
WHERE name = q.name

The alternative would be a subselect, but that would also try to update all names, not only those that exist in historical:

UPDATE names
SET min_price = (SELECT MIN(price) FROM historical WHERE historical.name = names.name)

---

Related Topics