CODEWITHSUNDEEP
postgresql
Eugen Konkov
Eugen Konkov

Reputation: 25167

How to rewrite query?

I can easily select rows which I should update.

select 
   p.id, 
   (regexp_match( p.name, '\d+'))[1] as renum, 
   pd.quantity 
from package p 
left join package_detail pd on 
   pd.package_id = p.id and resource_type_id is null 
where p.name like '%Bit%';

But how to write query to update quantity by renum from the result above?

I am not looking for the query. I am looking the rule to complete this task.

Upvotes: 0

Views: 47

Answers (1)

George S
George S

Reputation: 2151

You can find background in the docs (https://www.postgresql.org/docs/current/sql-update.html ) but if you've got a query that gives the result you want, you can use that query as a correlated subquery in the update command:

UPDATE table1 t1 SET (col1, col2, col3) = (select t2.val1, t2.val2, t2.val3 from table2 t2 where t2.table1_id = t1.id)
 WHERE t1.col1 IS NULL

In your case, this might take the form or something similar to: 

UPDATE package p2 SET (quantity) = (
        select ((regexp_match( p.name, '\d+'))[1])::integer + pd.quantity 
        from package p 
         left join package_detail pd on pd.package_id = p.id and resource_type_id is null 
        where p.name like '%Bit%' 
         and p2.id = p.id
         and ((regexp_match( p.name, '\d+'))[1])::integer + pd.quantity IS NOT NULL )
 WHERE p2.name like '%Bit%'

Upvotes: 1

Related Questions