Inserting value in middle table in sqlite

Question

I am trying to insert a value in a middle table, which is created by having two other tables relationship many to many. So this middle table has 2 foreign keys(idProduct and idWarehouse), one for each table and an additional row(storage). So I have trouble inserting in this third row. It gives this error:

    Result: near "SELECT": syntax error
At line 1:
INSERT INTO ProductsInWarehouse (idProduct, idWarehouse, storage) 
VALUES 
(SELECT

This is my code:

    INSERT INTO ProductsInWarehouse (idProduct, idWarehouse, storage) 
VALUES 
(SELECT id FROM Products
WHERE nameProduct = 'cheese',
SELECT id FROM Warehouse
WHERE nameWarehouse = 'Vegas', 
'10')

See [this answer](https://stackoverflow.com/questions/25969/insert-into-values-select-from). Note such insert statements do not use `VALUES`, that's probably your error. The answer is db agnostic, but [SQLite documentation](https://www.tutorialspoint.com/sqlite/sqlite_sub_queries.htm) says the same. — pafau k., May 31 '20 at 12:55
I do not understand, in the links you provided they also use VALUES and also put SEARCH queries in parenthesis. — Escape, May 31 '20 at 14:35

forpas · Accepted Answer · 2020-05-31T14:46:43.700

Each of the queries must be enclosed inside parantheses:

INSERT INTO ProductsInWarehouse (idProduct, idWarehouse, storage) VALUES 
(
  (SELECT id FROM Products WHERE nameProduct = 'cheese'),
  (SELECT id FROM Warehouse WHERE nameWarehouse = 'Vegas'), 
  '10'
);

This will work if theses queries return always only 1 row, so both columns nameProduct and nameWarehouse must be unique.
The statement can also be written without the VALUES clause, with SELECT:

INSERT INTO ProductsInWarehouse (idProduct, idWarehouse, storage) 
SELECT
  (SELECT id FROM Products WHERE nameProduct = 'cheese'),
  (SELECT id FROM Warehouse WHERE nameWarehouse = 'Vegas'), 
  '10';

Yeah, this was it :) Thanks a lot! – Escape May 31 '20 at 14:48 — Escape, May 31 '20 at 14:48

Inserting value in middle table in sqlite

1 Answers1