Insert one value into a table with all values from another table-CodePudding

INSERT INTO inventory (film_id, store_id)
VALUES ((SELECT film_id FROM film WHERE title='EUCLIDEAN PI'), (SELECT store_id from store));

This doesn't work. The error returned is "more than one row returned by a subquery used as an expression".

Basically my inventory is supposed to have a copy of this specific film for every single store. If the id of ECULIDEAN PI is 347. The inventory table should show a copy of 347 for every single store_id in the store table.

film_id store_id  
347     1  
347     2  
347     3  
347     n

Where n is the last store_id. Store_id may not always be incremental.

CodePudding user response：

You don't mention which specific database are you using, and the syntax changes a bit between them.

I'll assume it's for SQL Server. If that's the case, you can do:

insert into inventory (film_id, store_id)
select
  (select film_id from film where title = 'EUCLIDEAN PI'), 
  store_id
from store;

Result:

 film_id  store_id 
 -------- -------- 
 347      1        
 347      2        
 347      3        
 347      7        
 347      10

See running example at db<>fiddle.