Clever Geek Handbook

INSERT with two SELECT statements

Here's what I'm trying now, but I get mySQL error:

mysql_query ("INSERT INTO profile_tag (profile_id, tag_id) 
(SELECT profile_id FROM profile WHERE username = '$username'), 
(SELECT tag_id FROM  tag WHERE  tag = '$music' OR tag = '$sports' OR tag = '$tech')");

I can accomplish INSERT

using one statement SELECT

, but not two.

The error I am getting:

The request is invalid. You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the correct syntax to use next to '(SELECT tag_id FROM tag WHERE tag = '' OR tag = 'sports' OR tag = '')'

in line 1

+3


source to share


3 answers


As with the error, the syntax is incorrect. The insert values ​​must match the number of values ​​in the column definition.

INSERT INTO profile_tag (profile_id, tag_id)
SELECT
    profile_id, tag_id
FROM
    profile
    CROSS JOIN tag
WHERE
    username = ?
    AND tag IN (?, ?, ?)


Note that this will add multiple lines if a value is found for each of the inputs tag

, but I believe this is what you want.

+3


source


You can use the sentence VALUES 

insert into profile_tag(user_id, tag_id) values
((select id from users where username = 'alice'),
 (select id from tags where tag = 'music'));


http://sqlfiddle.com/#!2/76439/1/0

+1


source 


mysql_query ("INSERT INTO profile_tag (profile_id, tag_id) 
(SELECT profile.profile_id, tag.tag_id FROM profile LEFT JOIN tag ON 1 WHERE profile.username = '$username' AND tag.tag IN ('$music', '$sports', '$tech'))");
0


source






More articles:


All Articles