How can I insert data into a table from a query which return multiple statement?

Question

INSERT INTO admin(UserID,Username,InitialBid) select ID,username,(select  
    case
    when experience = 'Fundamental' then '1'
    when experience = 'Novice' then '2'
    when experience = 'Intermediate' then '3'
    when experience = 'Advanced' then '4'
    when experience = 'Expert' then '5'
end as intbid
from user_details ) from user_details;

In this code I want to add data from one table to another along with a conditional statement column as a sub-query. As it returns multiple rows at a time so it is not able to execute this query. What should I write that we got value in InitialBid column corresponding to its value?

Answer 1

I really doubt that you want a subquery at all. Doesn't this do what you want?

insert into admin (UserID, Username, InitialBid) 
select id, username,
    case experience
        when 'Fundamental'  then 1 
        when 'Novice'       then 2 
        when 'Intermediate' then 3 
        when 'Advanced'     then 4 
        when 'Expert'       then 5 
    end as intbid 
from user_details;

This inserts one row in the target table for each row in user_details, while transcoding the experience to a literal number.

Notes:

• a short-circuit case expression is good enough in this situation (the column is listed only once, at the beginning of the expression)

• initialBid looks like a numeric column; if so, remove the single quotes around the literal values that are generated by the case expression, so the database does not need to implicitly cast them.