MySQL transaction query help

Question

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

Answer 1

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]';

Answer 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]'