Обсуждение: Merging 2 rows in a table
Hello, through some obscure error (probably on my side) I have several thousand entries for Jan 1 and Jan 2 ending up in the ISO week 2011-52 instead of 2010-52 which breaks the bar chart at the top of my script http://preferans.de/user.php?id=OK504891003571 # select * from pref_money where id='OK324712148886'; id | money | yw ----------------+-------+--------- OK324712148886 | 203 | 2010-46 OK324712148886 | 219 | 2010-49 OK324712148886 | 115 | 2010-51 OK324712148886 | 63 | 2010-52 OK324712148886 | 20 | 2011-01 OK324712148886 | 10 | 2011-52 # \d pref_money Table "public.pref_money" Column | Type | Modifiers --------+-----------------------+----------------------------------------- id | character varying(32) | money | integer | not null yw | character(7) | default to_char(now(), 'YYYY-IW'::text) Indexes: "pref_money_yw_index" btree (yw) Foreign-key constraints: "pref_money_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) I would like to fix that problem by adding 2011-52 values of money (10 above) to the 2010-52 values of money (63 above, should become 73) and then dropping all rows with the wrong week number yw='2011-52'. So I'm trying: # update pref_money set money=money+ (select money from pref_money where yw='2011-52') where yw='2010-52'; ERROR: more than one row returned by a subquery used as an expression ok, I probably need to specify the id for the subquery in round brackets above - so I try again: # update pref_money as m1 set money=money+ (select coalesce(money,0) from pref_money as m2 where m1.id=m2.id and m2.yw='2011-52') where m1.yw='2010-52'; ERROR: null value in column "money" violates not-null constraint Can anybody please help me here? Thank you Alex
2011/1/3 Alexander Farber <alexander.farber@gmail.com>: > Hello, > > through some obscure error (probably on my side) > I have several thousand entries for Jan 1 and Jan 2 > ending up in the ISO week 2011-52 instead of 2010-52 > which breaks the bar chart at the top of my script > http://preferans.de/user.php?id=OK504891003571 > > # select * from pref_money where id='OK324712148886'; > id | money | yw > ----------------+-------+--------- > OK324712148886 | 203 | 2010-46 > OK324712148886 | 219 | 2010-49 > OK324712148886 | 115 | 2010-51 > OK324712148886 | 63 | 2010-52 > OK324712148886 | 20 | 2011-01 > OK324712148886 | 10 | 2011-52 > > # \d pref_money > Table "public.pref_money" > Column | Type | Modifiers > --------+-----------------------+----------------------------------------- > id | character varying(32) | > money | integer | not null > yw | character(7) | default to_char(now(), 'YYYY-IW'::text) > Indexes: > "pref_money_yw_index" btree (yw) > Foreign-key constraints: > "pref_money_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) > > I would like to fix that problem by adding > 2011-52 values of money (10 above) to the > 2010-52 values of money (63 above, should become 73) > and then dropping all rows with the > wrong week number yw='2011-52'. > > So I'm trying: > > # update pref_money set money=money+ > (select money from pref_money where yw='2011-52') > where yw='2010-52'; > ERROR: more than one row returned by a subquery used as an expression > > ok, I probably need to specify the id for the subquery > in round brackets above - so I try again: > > # update pref_money as m1 set money=money+ > (select coalesce(money,0) from pref_money as m2 > where m1.id=m2.id and m2.yw='2011-52') > where m1.yw='2010-52'; > ERROR: null value in column "money" violates not-null constraint update tab set money = money + COALESCE((SELECT ...) , 0) .. Regards Pavel Stehule > > Can anybody please help me here? > > Thank you > Alex > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Thank you Pavel, has worked: # update pref_money as m1 set money=money+coalesce((select money from pref_money as m2 where m1.id=m2.id and m2.yw='2011-52'),0) where m1.yw='2010-52'; UPDATE 2081
On 2011-01-03, Alexander Farber <alexander.farber@gmail.com> wrote: > Hello, > > through some obscure error (probably on my side) > Column | Type | Modifiers > --------+-----------------------+----------------------------------------- > id | character varying(32) | > money | integer | not null > yw | character(7) | default to_char(now(), 'YYYY-IW'::text) 'YYYY-IW' above should be 'IYYY-IW' (you may have made a similar mistake in other places too.) > I have several thousand entries for Jan 1 and Jan 2 > ending up in the ISO week 2011-52 instead of 2010-52 > which breaks the bar chart at the top of my script > http://preferans.de/user.php?id=OK504891003571 > I would like to fix that problem by adding > 2011-52 values of money (10 above) to the > 2010-52 values of money (63 above, should become 73) > and then dropping all rows with the > wrong week number yw='2011-52'. perhaps something like this: update pref_money as dat set money=dast.money+ foo.money from pref_money as foo where dat.id=foo.id and dat.yw='2011-52' and foo.yw ='2012-52' ; -- ⚂⚃ 100% natural
This explains my problem, thanks! On Mon, Jan 3, 2011 at 7:52 PM, Jasen Betts <jasen@xnet.co.nz> wrote: > On 2011-01-03, Alexander Farber <alexander.farber@gmail.com> wrote: >> through some obscure error (probably on my side) > >> Column | Type | Modifiers >> --------+-----------------------+----------------------------------------- >> id | character varying(32) | >> money | integer | not null >> yw | character(7) | default to_char(now(), 'YYYY-IW'::text) > > 'YYYY-IW' above should be 'IYYY-IW'