0

I have two tables

main table is

Complaintid Status  Templateid
123           2 
135           5      2
142           5      8
143           3 
145           5 

Second table

Parent_complaint_id Currentstatus   Reopen  Photo
123                     0             0     Abc.jpg
123                     2             0     Abc.jpg
135                     5             0     Xyz.jpg
142                     5             0     Sim.jpg
143                     3             0     123.jpg
145                     2             0     Ing.jpg
145                     5             0     Ing.jpg

After updation first table

Complaintid Status  Templateid
123          2  
135          1       2
142          1       8
143          3  
145          5  

After updation second table

Parent_complaint_id Currentstatus   Reopen  Photo
123                  0                0     Abc.jpg
123                  2                0     Abc.jpg
135                  1                0     Xyz.jpg
142                  1                0     Sim.jpg
143                  3                0     123.jpg
145                  2                0     Ing.jpg
145                  5                0     Ing.jpg

Here is the first query

Update complaints 
set status='1' 
where templateid is not null 

but i don't know how to add subquery in above query

0

For example

UPDATE second_table
SET Currentstatus = 1
WHERE EXISTS ( SELECT 1
               FROM first_table
               WHERE first_table.Complaintid = second_table.Parent_complaint_id
                 AND first_table.templateid is not null )

Or

UPDATE second_table
SET Currentstatus = 1
FROM first_table
WHERE first_table.Complaintid = second_table.Parent_complaint_id
  AND first_table.templateid is not null

Or ... too many variants.

  • I have to update both tables – sumit kundan May 13 at 10:47
  • 1
    @sumitkundan Your "Here is the first query" successfully updates first table. Then use one of my queries to update second table. Wrap both queries into a transaction if needed. Anycase one query can update one table only, so you cannot to update both tables with one query. – Akina May 13 at 10:53
  • @sumitkundan Of course you can try to move the first update to CTE with adding proper RETURNING clause. – Akina May 13 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.