Kapil Praksh Kapil Praksh - 5 months ago 11
SQL Question

how to use update as inner subquery in postgresql

How to use update as a inner subquery in postgresql?

update unit_has_jobcard
set status = 'approval'
where id = (update jobcard_has_approvals
set approve = true
where id = 27
returning id);


I need to update two tables in single query.
addBatch
helps multiple queries hit a table in single time. But i need to know if is this possible.

Answer

You need a common table expression to update two tables in a single statement:

with approvals as (
  update jobcard_has_approvals 
     set approve = true 
  where id = 27 
  returning id  
)
update unit_has_jobcard
  set status = 'approval' 
where id = (select id from approvals);
Comments