ZJAY ZJAY - 26 days ago 9
SQL Question

Truncate multiple select and update clauses into a single clause

The below query seems inefficient, as all I am doing is swapping out one variable (cobrand) for each query. Is there a way to consolidate this query into one clause and get the same result?

UPDATE temp_08.members
SET distinct_count=
(select distinct_count
from temp_08.members
WHERE cobrand='10001372' and month = '2016-09')
WHERE cobrand='10001372' and month = '2016-10' or month = '2016-11';


UPDATE temp_08.members
SET distinct_count=
(select distinct_count
from temp_08.members
WHERE cobrand='10006164' and month = '2016-09')
WHERE cobrand='10006164' and month = '2016-10' or month = '2016-11';



UPDATE temp_08.members
SET distinct_count=
(select distinct_count
from temp_08.members
WHERE cobrand='10005640' and month = '2016-09')
WHERE cobrand='10005640' and month = '2016-10' or month = '2016-11';



UPDATE temp_08.members
SET distinct_count=
(select distinct_count
from temp_08.members
WHERE cobrand='10005244' and month = '2016-09')
WHERE cobrand='10005244' and month = '2016-10' or month = '2016-11';

Answer

Use Postgres' update-with-join syntax:

UPDATE temp_08.members
SET distinct_count = dc
FROM (SELECT cobrand, distinct_count dc
      FROM temp_08.members
      WHERE month = '2016-09') x
WHERE temp_08.members.cobrand = x.cobrand
AND month IN ('2016-10', '2016-11')

You can add this to the inner query if you only want to update certain cobrands:

AND cobrand IN ('10001372', '10006164', '10005640', '10005244')