CODEWITHSUNDEEP
sqloracle-database
latala
latala

Reputation: 17

How to rewrite a SQL query that contains repeated SubQuery?

Could anyone help me to rewrite a SQL query as below in short ? There is a subquery to be repeated.

update policy 
set totalvehicles = (
  select count(*) from riskunit 
  where riskunit.policyId = policy.id
  and riskunit.subtype = 7) 
where policy.verified = '1' 
and policy.Totalvehicles <(
  select count(*) 
  from riskunit 
  where riskunit.policyId = policy.id 
  and riskunit.subtype = 7
);

Thanks !!

Upvotes: 0

Views: 236

Answers (2)

fancyPants
fancyPants

Reputation: 51888

This should work (assuming MySQL, also working for Oracle):

update policy p
inner join (
  select policyId, count(*) as n from riskunit 
  where riskunit.subtype = 7
  group by policyId
) ru on ru.policyId = p.id
set p.totalvehicles = ru.n
where p.verified = '1' 
and p.Totalvehicles < ru.n;

Upvotes: 2

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

I prefer this because it's easy to insert a select above the from and see what would be changed.

UPDATE p 
SET totalvehicles = cnt.[Count]
FROM policy p
INNER JOIN (
    SELECT
        policyId,COUNT(*) [Count]
    FROM riskunit
    WHERE ru.subtype = 7
    GROUP BY policyId
) cnt on cnt.policyId=p.policyId
WHERE p.verified = '1'
AND p.Totalvehicles < cnt.[Count]

Upvotes: 2

Related Questions