Henra Henra - 1 year ago 98
SQL Question

INSERT value using SELECT in mysql

I have 2 tables: users with columns (id,username, password), and user_failed with columns (user_id, failed, time). is there any possible way i can insert into table user_failed by only using username? i try this code but it failed:

INSERT INTO `login_attempts`(`user_id`, `time`, `failed`)
VALUES (SELECT user_id FROM users WHERE username = 'pokemon','',3)

Answer Source

Your SQL query is incorrect for several reasons.

The following should work if I have interpreted your query correctly.

INSERT INTO `login_attempts`(`user_id`, `time`, `failed`)
SELECT id, '', 3 FROM users WHERE username = 'pokemon'

INSERTing into a table from a SELECT does not require VALUES ( ... ). Here is an example of how you would use VALUES ( ... ):

INSERT INTO `login_attempts`(`user_id`, `time`, `failed`)
VALUES (1, '', 3)

Also, your sub query SELECT user_id FROM users WHERE username = 'pokemon','',3 the WHERE clause is invalid. Specifically the '',3 part which I assume is the values you wanted to insert for time and failed.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download