should work. you could move b1 out of the sub query and add a normal where clause to make the syntax nicer. Might also matter for the query plan. other than that i don't see any bumps. It's good that you placed "b2.org_specific_rule = true" in the join clause so that the left join works properly. why the mixed feelings?
WBL 2012/4/19 Ondrej Ivanič <ondrej.iva...@gmail.com> > Hi, > > I have the following table: > org_id | contract_name | org_specific_rule | count > ----------+------------------+-------------------+------- > smpj28p2 | Group 123 | f | 3 > smpj28p2 | Group 2 | f | 3 > smpj28p2 | Group 2 | t | 9 > smpj28p2 | Group 1 | f | 1 > w37mtn4r | Group 123 | f | 26 > w37mtn4r | Group 2 | f | 56 > w37mtn4r | Group 1 | f | 55 > > Based on org_specific_rule and (org_id, contract_name) I need to transform > this: > smpj28p2 | Group 2 | f | 3 > smpj28p2 | Group 2 | t | 9 > > to > smpj28p2 | Group 2 | 9 > > in other words: > - if org_specific_rule = t then update "count" value in row where > org_specific_rule = f to value from this row (3 was updated to 9) > - remove org_specific_rule column > > I have query which does everything but I have mixed feelings about it: > select > b1.org_id, b1.contract_name, coalesce(b2.count, b1.count) as count > from (select * from billing where org_specific_rule = false) as b1 > left join billing b2 on > b1.org_id = b2.org_id > and b1.contract_name = b2.contract_name > and b2.org_specific_rule = true > order by 1,2; > > org_id | contract_name | count > ----------+------------------+---------- > smpj28p2 | Group 123 | 3 > smpj28p2 | Group 2 | 9 > smpj28p2 | Group 1 | 1 > w37mtn4r | Group 123 | 26 > w37mtn4r | Group 2 | 56 > w37mtn4r | Group 1 | 55 > > Any ideas? > > -- > Ondrej Ivanic > (ondrej.iva...@gmail.com) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw
