-2

This problem is baffling me:

BEGIN;
INSERT INTO sub_users(user_id, email) 
SELECT user_id FROM users WHERE email='[email protected]', '$email';
COMMIT;

Normally, I have multiple statements in that transactions, but I've removed those for clarity.

I get this error:

#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 ' '[email protected]'' at line 1

I'm using MySQL version 5.1.36

Improve this question
2
  • I might have to start a new post as this one seems to be confusing everyone. I simply want to select ONE value from the users table, then insert that and the user defined value of $email into the table sub_users. Commented Oct 26, 2009 at 3:03
  • No need for a new post; please re-read replies carefully. Commented Oct 26, 2009 at 3:11

2 Answers 2

Reset to default
2

Two problems with your statement:

  1. You're not selecting email to insert (only id)
  2. You're using '=' instead of IN clause.

Should be something like:

INSERT INTO sub_users(user_id, email)
 SELECT user_id, email FROM users
  WHERE email IN ('[email protected]', '[email protected]');

instead.

Update (based on comment)

INSERT INTO sub_users(user_id, email)
 SELECT user_id, '[email protected]' FROM users
  WHERE email = '[email protected]';
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

The email I want to insert is in the table I'm selecting from.
You're selecting from users. The above query will select all records from users whose email field is '[email protected]' or '[email protected]' and insert those records into sub_users. If you want to do something else, please clarify your question
Ok, I want to take the user_id of the user with email [email protected], then insert that into the sub_users table along with a new email ([email protected]). So [email protected] will come from a php variable, not the database.
I see. You need to specify your new email as constant value (or a parameter you're going to bind) in select clause. I've updated my answer above with a sample select.
[email protected] should not come from any mysql table. It will be defined by the user so it will be PHP variable.
|
2

You have at least two errors that I can see:

  1. You are trying to insert two columns worth of data (user_id and email) but only selecting one column (user_id) from the second half of INSERT INTO ... SELECT. You must select the same number of columns as you are trying to insert, and order matters.

  2. You have a syntax error in the predicate of your SELECT. I think you want to use:

    WHERE email IN ('[email protected]', '[email protected]')

or its equivalent:

WHERE email = '[email protected]'
   OR email = '[email protected]'
Improve this answer

6 Comments

But what if the second value I want to insert is not to come from the select. What do I do then?
I only need the user_id from the users table.
@Chad, you can do INSERT INTO sub_users (user_id, email) SELECT user_id, 'some constant' FROM .... 'some constant' can also be a function or another field from the table you're selecting from.
no, the email will not come from any table, it will be from a PHP variable.
I've reposted here: stackoverflow.com/questions/1622916/simple-mysql-query-question
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.