Retrieve a value from a SQLite INSERT INTO operation

Question

I am building an SQL database with SQLite3 and the Haskell library HDBC to connect to the database from my program. It's used for tracking sales of products (ie. on an EPOS). The schema is roughly like this, amended for brevity:

create table products (
    product_id    integer primary key autoincrement,
    product_name  text,
    product_price integer
);

create table sales (
    sales_id    integer primary key auto increment
    sales_date  date,
    number_sold integer
);

create table products_sales_xref (
    product_id integer references products,
    sales_id   integer primary key references sales
);

I want to insert a value into sales (ie, when a sale is logged/made) and then be able to take the value of the auto-incremented sales_id and use that in an insert into the cross-referencing table. I tried something like:

INSERT INTO products_sales_xref (product_id, sales_id) VALUES (2, 
  (INSERT INTO sales (sales_date, number_sold)
     VALUES ('2022-08-05', 25) RETURNING sales_id)
);
-- or
INSERT INTO products_sales_xref (product_id, sales_id)
SELECT 2, * from (
  INSERT INTO sales (sales_date, number_sold) VALUES ('2022-08-05', 25) RETURNING sales_id
);

which fails because of a parse error: Parse error: near "INSERT": syntax error. It seems like I can't use INSERT in a subquery? Is there a way I can use this value in the xref insert statement? I'd rather not use the get_last_rowid in case of a new value somehow getting in between the first insert and the second one.

score 0 · Answer 1 · answered Sep 14 '22 at 10:33

0

One statement can INSERT into ONE table!

answered Sep 14 '22 at 10:33

PChemGuy

1,582
3
6
18

...so what do I do instead? there's no point in "answering" like this – Lilly Cham Sep 14 '22 at 12:35
You do first INSERT with RETURNING, then make a second INSERT. It is not possible to have 2 INSERTS in one statement. – PChemGuy Sep 14 '22 at 14:12

Retrieve a value from a SQLite INSERT INTO operation

1 Answers1