updating table rows in postgres using subquery
Postgres allows:
UPDATE dummy
SET customer=subquery.customer,
address=subquery.address,
partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
FROM /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;
This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.
Postgresql - Update a table according to a subquery
exists
returns true or false depending on whether the subquery returns any rows. So, if the subquery returns any rows, then every row has true.
The issue is that you need to connect the outer table to the subquery. One method is a correlation clause:
update table1 t
set value1 = some_value,
value2 = some_value
from (subquery) s
where s. = t.;
is a column that uniquely identifies each row in table1
.
It is also possible that your subquery is of the form:
select . . .
from table1 t1 join
. . .
on . . .
. . .
If so, you can restructure the subquery and still use exists
:
update table1 t
set value1 = some_value,
value2 = some_value
from (subquery) s
where exists (select 1
from . . . -- no table1
where
);
updating table rows in postgres using subquery with jooq
Assuming you're using code generation, do it like this:
Table> subquery = table(
select(
DUMMY.ADDRESS_ID,
DUMMY.CUSTOMER,
DUMMY.ADDRESS,
DUMMY.PARTN
)
.from(DUMMY)
).as("subquery");
ctx.update(DUMMY)
.set(DUMMY.CUSTOMER, subquery.field(DUMMY.CUSTOMER))
.set(DUMMY.ADDRESS, subquery.field(DUMMY.ADDRESS))
.set(DUMMY.PARTN, subquery.field(DUMMY.PARTN))
.from(subquery)
.where(DUMMY.ADDRESS_ID.eq(subquery.field(DUMMY.ADDRESS_ID)))
.execute();
Of course, the query makes no sense it is, because you're just going to touch every row without modifying it, but since you copied the SQL from another answer, I'm assuming your subquery's dummy
table is really something else.
PostgreSQL - Update table with subquery from updated table itself
SQL Fiddle
with lang as (
select distinct on (user_id)
user_id, text_lang, text_lang_count
from (
select
user_id,
text_lang,
count(text_lang) as text_lang_count
from t
group by user_id, text_lang
) s
order by 1, 3 desc, text_lang != 'en' -- in a tie english wins
)
update t
set user_lang = lang.text_lang
from lang
where t.user_id = lang.user_id
Calculate the predominant language in the CTE (with
) and use it in the from
clause
PostgreSQL update from subquery
Not too efficient ,but if that's not an issue you can do this :
UPDATE users u
SET u.created_by = (SELECT t.user_id FROM users t
WHERE u.created_by_old = t.user_old_id),
u.last_updated_by = (SELECT s.user_id FROM users s
WHERE u.last_updated_by = s.user_old_id)
Update values from subquery for every single row
The issue is that Postgres decides to do you a favor by optimizing the subquery and running it only once. Although Postgres is not the only database that does this, I consider this an error because the subquery depends on a volatile function. The optimization changes the meaning of the code.
I have found that a correlation clause -- of almost any type -- to the outer query fixes the problem. For instance, if you have a non-NULL
id column:
update users u
set first_name = (select firstname from temp_names tn where u.id is not null order by random() limit 1),
last_name = (select lastname from temp_names where u.id is not null order by random() limit 1);
Here is a db<>fiddle.
update table only if subquery returns the result postgres
I would use the update/join syntax here, so nothing is updated if the subquery returns no row:
update public.test t
set geom = t1.geom
from (
select geom
from public.test
where st_geometrytype(geom) = 'X'
limit 1
) t1
As for the query you wanted to write, using coalesce()
: you would need to surround the subquery with parentheses, so it is explicit that it returns a scalar:
update public.test t
set geom = coalesce(
(select geom from public.test where st_geometrytype(geom) = 'X' limit 1),
geom
)
However this approach is less efficient, because it still updates all rows of the table back to their original value if the subquery returns no rows; the update/join approach is preferrable in that regard.
Note, however, that limit
without order by
makes little sense, because it is not deterministic; when the subquery produces several rows, it is undefined which one will be picked.
Related Topics
Nvarchar(Max) Still Being Truncated
Decision Between Storing Lookup Table Id'S or Pure Data
SQL Server Convert Varchar to Datetime
Using Isnull VS Using Coalesce for Checking a Specific Condition
Convert Comma Separated Column Value to Rows
Regex to Select Nth Value from a List, Allowing for Nulls
Access-Sql: Inner Join With Multiple Tables
How to Set a Maximum Execution Time For a MySQL Query
How to Determine the Number of Days in a Month in SQL Server
The Maximum Recursion 100 Has Been Exhausted Before Statement Completion
Log Every SQL Query to Database in Rails
How to Render All Records from a Nested Set into a Real HTML Tree
Using 'Case Expression Column' in Where Clause