Help with MySQL insert query?

Question

I'm having some trouble with an insert using a subquery.  I have it half working... my problems start when I try to add information into additional fields that aren't pulled by the subquery.

What I have right now:

INSERT INTO `phpbb_users` 
	(user_id, username, user_password, user_regdate, user_email, user_website, user_from, user_avatar) 
	(SELECT u.id, u.forum_user, u.password, UNIX_TIMESTAMP(u.date_created), u.email, p.web, p.hometown, u.avatar FROM users u JOIN profiles p ON u.id=p.user_id WHERE u.id = 4)

Now that all works just fine.  But I also need to add info that isn't already stored elsewher in the database into some additional fields in the phpbb_users table.  So I came up with this:

INSERT INTO `phpbb_users` 
	(user_id, username, user_password, user_regdate, user_email, user_website, user_from, user_avatar, user_avatar_type, user_notify, user_notify_pm, user_popup_pm, user_timezone, user_attachsig) 
	((SELECT u.id, u.forum_user, u.password, UNIX_TIMESTAMP(u.date_created), u.email, p.web, p.hometown, u.avatar FROM users u JOIN profiles p ON u.id=p.user_id WHERE u.id = 4), 2, 0, 0, 1, '-5.00', 1)

Unfortunately, this gives me a syntax error, and I'm not sure where my syntax is screwed up.  If it helps, the error I'm getting is as follows:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT u.id, u.forum_user, u.password, UNIX_TIMESTAMP(u.date_created), u.email,' at line 3

conventional · Accepted Answer

Instead of adding the values after the select clause, you add them in the select clause, like so:

SELECT .... p.hometown, u.avatar, '2', '0', '0', '1', '-5.00', '1' FROM users u ....

no1home2day · Answer

You can use anything in the SELECT clause.  For instance, if you have information in a text box, you could include "SELECT ' " & myText.Text & " ', u.date_Created, 'This Literal Value within single quotes', <etc> ... "

Note that you need a single quote around the value coming from myText.Text (myText is the name of a text box with information to be stored), so you would include the ' prior to the " before the myText, and again include ' after the " so the entire text will look like: "SELECT 'information typed into box', etc "

I fear that I'm being as clear as mud in my explanation.
The point is, you can use just about anything in the SELECT information, including literal values, variables, etc.

Anonymous · Answer

I save seeing extra worry-unfastened questions from you... you do no longer look to soak up attention what clientele inform you. and you certainly would desire to study "heavily" approximately MySQL: your querries are those of genuine beginner. study first.

Holy Cow! · Answer

It looks like you need to use an IN( ) statement or a from statement to me.

&quot;David&#39;s Run&quot; · Answer

Yay !! Now choose,Please :))

Help with MySQL insert query?

5 Answers