Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

Help with MySQL insert query?

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

Update:

Yay! It works now! Thanks, truthbetold and noonehome2day.

5 Answers

Relevance
  • 1 decade ago
    Favorite 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 ....

  • 1 decade ago

    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
    4 years ago

    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.

  • 1 decade ago

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

  • How do you think about the answers? You can sign in to vote the answer.
  • 1 decade ago

    Yay !! Now choose,Please :))

Still have questions? Get your answers by asking now.