为Circus表新增ticket_sold列并按circus_id统计售票数报错解决

错误根源 #

你写的UPDATE语句里，子查询select count(ticket_id) from ticket group by circus_id会返回两条结果（对应两个马戏场次的售票数），但更新时每一行的ticket_sold只能接受单个值，所以触发了single-row subquery returns more than one row这个错误。

两种可行的修正方案 #

方案1：给子查询加关联条件 #

让子查询只返回当前马戏场次对应的售票数，通过WHERE子句关联两张表的circus_id：

-- 新增列的语句没问题，保留
alter table circus add ticket_sold numeric(3) default 0;

-- 修正后的更新语句
update circus 
set ticket_sold = (
    select count(ticket_id) 
    from ticket 
    where ticket.circus_id = circus.circus_id
);

方案2：用JOIN方式更新 #

通过关联查询预先统计好每个场次的售票数，再和Circus表关联更新，这种写法在大部分数据库里性能更优：

alter table circus add ticket_sold numeric(3) default 0;

update circus c
inner join (
    select circus_id, count(ticket_id) as total_sold
    from ticket
    group by circus_id
) t on c.circus_id = t.circus_id
set c.ticket_sold = t.total_sold;

额外优化：处理无售票记录的场次 #

如果有些马戏场次还没卖出票，方案1会把ticket_sold设为NULL，要是想让它保持默认的0，可以用COALESCE函数兜底：

update circus 
set ticket_sold = COALESCE(
    (select count(ticket_id) from ticket where ticket.circus_id = circus.circus_id),
    0
);

内容的提问来源于stack exchange，提问作者Hanzcerb