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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
