2011/1/3 Alexander Farber <alexander.far...@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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
