PostgreSQL：将一行的值添加到另一行并删除前一行

Question

通过一些模糊的错误（可能在我这边），在年份的变化中，我的表中得到了值“2011-52”（列出了玩家在一周内赢得的虚拟货币），这些值实际上属于“2011-01” ：

# select * from pref_money where id='OK324712148886';
       id       | money |   yw
----------------+-------+---------
 OK324712148886 |   203 | 2010-46
 OK324712148886 |   219 | 2010-49
 OK324712148886 |   115 | 2010-51
 OK324712148886 |    63 | 2011-52
 OK324712148886 |    20 | 2011-01

要解决这个问题，我想将 yw='2011-52' 行的值 (63) 添加到 yw='2011-01' 行的值 (20)，然后删除前一行，对于表中的每个 id。

所以我正在尝试（使用 PostgreSQL 8.4.6 / CentOS 5.5）：

# update pref_money set money=money+
    (select money from pref_money where yw='2011-52') 
    where yw='2011-01';
ERROR:  more than one row returned by a subquery used as an expression

这可能是因为我需要在子查询的圆括号内指定 id ？请问我该如何解决我的问题？

谢谢你！ Alex

更新2：

我也尝试过：

# update pref_money set money=money+
(select money from pref_money m2 where m2.yw='2011-52' and id=m2.id) 
where yw='2011-01';
ERROR:  more than one row returned by a subquery used as an expression

和

# update pref_money m1 set m1.money=m1.money+
(select money from pref_money m2 where m2.yw='2011-52' and m1.id=m2.id) 
where m1.yw='2011-01';
ERROR:  column "m1" of relation "pref_money" does not exist
LINE 1: update pref_money m1 set m1.money=m1.money+(select money fro...

并且：

# 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='2011-01';
ERROR:  null value in column "money" violates not-null constraint

我的表定义是：

# \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)

当然我有数千行具有不同的id，或者我只是手动修复1个值并且不会'不要在 Stackoverflow 上提问。

我不同意这样的评论，即 2011-52 可能是 2011 年初的正确值。

Answer 1

您需要确保子查询恰好返回一行（如错误消息所示）。您的示例数据没有显示这一点，但显然您的表格中有不止一行带有 yw='2011-01' 的行。

如果 id 列是主键（或其一部分），那么将其添加到内部选择将纠正此问题。

update pref_money 
  set money=money +
       (select money 
          from pref_money m2 
         where m2.yw = '2011-52'
           and m2.id = pref_money.id)
where yw='2011-01';

Answer 2

嗯，这个 52 也许并不是真正的错误。 201 年的第一周在 ISO 年份中实际上编号为 52。有时是 0，有时是 52。 http://en.wikipedia.org/wiki/ISO_week_date 。

因此，也许您不想要本周的 ISO 编号，因为在按顺序查询时这可能会出现问题。或者尝试将 2011-52 更改为 2010-52。

并且不要合并 2011-52 和 2011-01，2011-01 从 03/01/2011 开始。 2011 年 1 月 1 日02/01/2011 确实是第 52 周！

Answer 3

一种可能性是使用聚合并执行类似的操作：

  update pref_money as pm 
     set money = money+money_delta
    from ( select id, sum(money) as money_delta 
             from pref_money as pmt 
            where yw > '2011-01' -- here you should include only the records, that are really corrupted
                                 -- for the example of the data from you, text > should work
            group by id) as pmd
   where pm.id = pmd.id;
     and pm.yw = '2010-01';

但说实话，我会为 yw 字段使用 date 数据类型并设置约束date_trunc('month', yw)::date = yw 以确保 yw 中的日期仅为该月的第一天。

Answer 4

我最终做了：

# 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

# delete from pref_money where yw='2011-52';
DELETE 1223

我应该首先使用 IYYY-IW 而不是 YYYY-IW - 从 [电子邮件受保护] 邮件列表