A Brief Intro
Have you ever wondering how to update multiple rows with one query? Let’s say
you want to change value on table A where the name is anu on year 2019,
2020, and 2021.
Rather than doing 3 query with different WHERE statement, we can do that
with one query. Without further ado, let’s go straight to it!
FROM statement
Postgresql has FROM clause in UPDATE statement which let us use columns on
other table, it use the same syntax as FROM clause in SELECT statement.
We can combine FROM clause with VALUES expression. VALUES in postgresql
let us create a constant table which means we can generate a table with
constant values that can be used in a query without having to actually create
and populate the table on disk.
Now here’s an example case:
Let’s say we need to update the price rows on area tokyo at table
meat with year 2019, 2020, and 2021. We can make the query like this:
UPDATE meat
SET price = new.price
FROM (values
(6942, 2019),
(69420, 2020),
(69690, 2021),
) AS new(price, year)
WHERE meat.area ILIKE 'tokyo'
AND meat.year = new.year
RETURNING *;
You can change the new in new(price, year) with anything you like.
Another example case:
Let’s say we need to update the city’s population on specific regencies.
So we need to update data on population column in city table which has
reference to regencies id. We can make the query like this:
UPDATE city
SET population = new.population
FROM
regencies,
(values
('city 1', 69420),
('city 2', 6969)
) AS new(regency, population)
WHERE regencies.id = city.regency_id
AND regencies.regency ilike new.regency
RETURNING regencies.regency, city.population;
The
RETURNINGclause is optional, it’s a way to check if we really changed the right rows or not. TheILIKEclause is an expression for insensitive case.
Alright, that’s it. See you next time!