CODEWITHSUNDEEP
mysqlsqlsql-insert
Ying
Ying

Reputation: 1422

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)

Upvotes: 6

Views: 6969

Answers (2)

vinay kumar
vinay kumar

Reputation: 164

This will work....you have to add plain parentheses before and after statements.

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

Upvotes: 1

Jonathon Ogden
Jonathon Ogden

Reputation: 1582

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.

Upvotes: 6

Related Questions