optimize the stored procedure with

Question

I want to optimize the below procedure by getting rid of the 3 times call to the same subquery. I thought I could make a temp table in a stored procedure and will update that, but don't know how to do that. any other ways to do this are welcome.

update some_table SET
status = (
case when 'OVERDUE' in (select status from other_table temp where Id = temp.Id)
then 'OVERDUE'
when 'UPCOMING' in (select status from other_table temp where Id = temp.Id)
then 'UPCOMING'
when 'PAID' in (select status from other_table temp where Id = temp.Id)
then 'PAID'
else 
null
END
)

Answer 1

If there is more than one record in other_table temp where Id = temp.Id then You can use the following query:

update some_table SET status = 
  (select case when count(case when status = 'OVERDUE' then 1 end) > 0 then 'OVERDUE'
               when count(case when status = 'UPCOMING' then 1 end) > 0 then 'UPCOMING'
               when count(case when status = 'PAID' then 1 end) > 0 then 'PAID'
          end        
     from other_table temp where Id = temp.Id)