CODEWITHSUNDEEP
sqlhsqldb
rapt
rapt

Reputation: 12230

HSQLDB - ON DUPLICATE KEY UPDATE, fails when UPDATE refers to SELECT columns

I am using HSQLDB 2.6.1. I am trying to use ON DUPLICATE KEY UPDATE in a stored procedure. Specifically, I want the UPDATE portion to refer to column values from the SELECT portion.

create table car(
    id int, 
    quantity int,
    primary key(id)
);

insert into car values
    (1, 100),
    (2, 200),
    (3, 300);

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 + car_quantity;

    end;

The above code throws an error when I try to create the procedure. The error says that car_quantity (in the UPDATE portion) is not recognized. When I replace car_quantity with a number, the procedure is created.

I have seen this answer and this documentation, and it seems to me like my code was supposed to work.

What is wrong with my code?

Upvotes: 0

Views: 77

Answers (1)

fredt
fredt

Reputation: 24372

The INSERT is for the table car. The new values for the column quantity can be specified using VALUES(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;

Upvotes: 1

Related Questions