Is there a way i can do multiple inserts into one table using a condition?

Question

Is there a way i can do multiple inserts into one table using a condition?

i have a list of subscribers in tbl_subscribers. i have an update on productX so i would like everyone who is subscribes to productX to get a notification. The user_notification table is id PK, user_id, notification_id. The two values i need is product_id (productX) which allows me to find a list of subscribers in tbl_subscribers and the notification_id to insert into the user_notification table.

How can i do this insert using one query? I see you can do a select statement in sqlite http://www.sqlite.org/lang_insert.html but i cannot wrap my head around how i may do this nor seen an example.

Answer 1

I believe you're looking from INSERT SELECT as outlined here:

http://www.1keydata.com/sql/sqlinsert.html

The second type of INSERT INTO allows us to insert multiple rows into a table. Unlike the previous example, where we insert a single row by specifying its values for all columns, we now use a SELECT statement to specify the data that we want to insert into the table. If you are thinking whether this means that you are using information from another table, you are correct. The syntax is as follows:

INSERT INTO "table1" ("column1", "column2", ...) 
SELECT "column3", "column4", ... FROM "table2"

Answer 2

insert into user_notification(user_id, notification_id)
select s.user_id, @notification_id 
from tbl_subscriber s 
where s.product_id = @productX