Sudoscience Sudoscience - 6 months ago 17
SQL Question

Insert into a different table for each result of a select query

If I have a setup such as:

set @idToIgnore = (select user_id from user_field_value where user_field_id = 82 and value = 'No';


Then I run a select query:

select id from users where account_id = 10 and id != @idToIgnore;


Which returns ~15 different ID values.

I then want to run a query such as:

insert into user_field_value(user_id, user_field_id, value) values (**user_id**, 82, 'Yes');


where user_id in the final insert query is each of the id's from the second query.

I assume there is an easy way to do this but I can't figure it out.

Answer

Why not just do that in one INSERT INTO ... SELECT ... query?

INSERT INTO user_field_value(user_id, user_field_id, value) 
SELECT id, 82, 'Yes' FROM users 
WHERE account_id = 10 and id != @idToIgnore;

You can also do the whole thing in one query using subquery instead of @idToIgnore, but be careful in the matter of performance.