HSQLDB中ON DUPLICATE KEY UPDATE引用SELECT列报错排查

你的问题出在HSQLDB的ON DUPLICATE KEY UPDATE语法规则上——在这个子句里，不能直接引用SELECT语句中的列名（比如car_quantity），必须用VALUES(column_name)函数来获取INSERT语句对应列的待插入值。

原因是ON DUPLICATE KEY UPDATE的作用域是目标表（这里是car），而非SELECT的源表（car_quantities），所以直接写car_quantity会被数据库判定为未定义的标识符。

修改后的存储过程代码如下：

create procedure insert_update_quantities(in new_quantity int)
        modifies sql data
    begin atomic

        declare table car_quantities (car_id int, car_quantity int);

        insert into car_quantities values
            (1, new_quantity),
            (2, new_quantity),
            (3, new_quantity),
            (4, new_quantity),
            (5, new_quantity);

        insert into car (id, quantity)
            select car_id, car_quantity from car_quantities
            on duplicate key update quantity = quantity + VALUES(quantity);

    end;

这里VALUES(quantity)对应的就是INSERT语句中quantity列的待插入值，也就是SELECT语句里的car_quantity，这样就能正确实现重复键时累加数量的逻辑了。

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