6

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)
DanMan
  • 11,323
  • 4
  • 40
  • 61
Ying
  • 1,282
  • 4
  • 19
  • 34

2 Answers2

6

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.

Jonathon Ogden
  • 1,562
  • 13
  • 19
  • it's can't i get this error #1054 - Unknown column 'user_id' in 'field list' – Ying Jul 23 '16 at 09:41
  • ah it's work, i just change user_id to id since in my users table it's id not user_id. thanks – Ying Jul 23 '16 at 09:42
1

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)
vinay kumar
  • 164
  • 6