INSERT INTO statement with SELECT... IN error

Question

Well, i need to add a row in user_badges for each person who had correctly respond to a poll. The

"select user_id from room_poll_results........" is working fine alone, but as soon as i try to use it in my INSERT INTO statement, it gives back an error:

"[Err] 1054 - Unknown column 'user_id' in 'IN/ALL/ANY subquery'"

I don't know where it's coming from...

INSERT INTO user_badges (user_id,PPO) SELECT user_id IN
(SELECT user_id FROM room_poll_results
WHERE user_id in (select user_id from room_poll_results 
where answer_text='3' AND question_id='3') AND user_id in
(select user_id from room_poll_results where answer_text='2' AND question_id='4'));

score 1 · Answer 1 · answered Jun 09 '13 at 13:13

1

It's telling you that there's no column called user_id in room_poll_results. Change that column name (in the subselects) to whatever is the appropriate field in the table. (You'd want to post the full schema for a more specific response.)

answered Jun 09 '13 at 13:13

Denis de Bernardy

75,850
13
131
154

http://gyazo.com/17819cc861b8acadff3cf4d9038e1fa3.png as this screen is showing, there is a user_id column in room_poll_results – user2468314 Jun 09 '13 at 13:15
Run the subqueries one by one, to see where the issue is coming from. – Denis de Bernardy Jun 09 '13 at 13:19

sarwar026 · Answer 2 · 2013-06-09T13:25:54.970

0

I assume user_badges table has a column user_id. Your first IN should be replaced by FROM. So, I've slightly modified your query as below:

INSERT INTO user_badges (user_id, PPO) 
SELECT user_id FROM
(   
    SELECT user_id FROM room_poll_results
    WHERE user_id in 
    (
        select user_id from room_poll_results 
        where answer_text='3' AND question_id='3'
    ) AS U
    AND user_id in
    (
        select user_id from room_poll_results 
        where answer_text='2' AND question_id='4'
    ) AS U
) AS U;

edited Jun 09 '13 at 13:25

answered Jun 09 '13 at 13:17

sarwar026

3,821
3
26
37

Thanks sarwar. Now another error is returned: [Err] 1248 - Every derived table must have its own alias – user2468314 Jun 09 '13 at 13:21
I got it. http://stackoverflow.com/questions/1888779/every-derived-table-must-have-its-own-alias. by this time, I am trying to update my answer – sarwar026 Jun 09 '13 at 13:23
By adding "x" at the end like this: INSERT INTO user_badges (user_id, PPO) SELECT user_id FROM ( SELECT user_id FROM room_poll_results WHERE user_id in (select user_id from room_poll_results where answer_text='3' AND question_id='3') AND user_id in (select user_id from room_poll_results where answer_text='2' AND question_id='4') ) x; - it works – user2468314 Jun 09 '13 at 13:28
The problem is the following one: [SQL] INSERT INTO user_badges (user_id, PPO) SELECT user_id FROM ( SELECT user_id FROM room_poll_results WHERE user_id in (select user_id from room_poll_results where answer_text='3' AND question_id='3') AND user_id in (select user_id from room_poll_results where answer_text='2' AND question_id='4') ) x; [Err] 1136 - Column count doesn't match value count at row 1 I don't know why, because if i add a row (user_id,PPO,1) i get an error. The third column isn't required normally... http://gyazo.com/2a4c9ef0cec5d88a461bdb0aabefd6d5.png – user2468314 Jun 09 '13 at 13:32

score 0 · Answer 3 · answered Jun 09 '13 at 13:18

Whenever you get errors as "[Err] 1054 - Unknown column 'user_id' in 'IN/ALL/ANY subquery'" just read it to mean that a COLUMN with the give name in parenthesis does not exist in your query.

To debug this, since you are have sub-queries, run each query independently then see their results. That will help you know what table has the missing column. Once you've got it then you can create the column and then unite your sub-queries and that's it!

INSERT INTO statement with SELECT... IN error

3 Answers3