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