Updating Table Rows in Postgres Using Subquery

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



Leave a reply



Submit