How to add a where clause in a MySQL Insert statem

Question

This doesn't work:

INSERT INTO users (username, password) VALUES ("Jack","123") WHERE id='1';

Any ideas how to narrow insertion to a particular row by id?

Answer 1

INSERT INTO users (id,username, password) 
VALUES ('1','Jack','123')
ON DUPLICATE KEY UPDATE username='Jack',password='123'

This will work only if the id field is unique/pk (not composite PK though) Also, this will insert if no id of value 1 is found and update otherwise the record with id 1 if it does exists.

Answer 2

Try this:

Update users
Set username = 'Jack', password='123'
Where ID = '1'

Or if you're actually trying to insert:

Insert Into users (id, username, password) VALUES ('1', 'Jack','123');

Answer 3

A conditional insert for use typically in a MySQL script would be:

insert into t1(col1,col2,col3,...)
select val1,val2,val3,...
  from dual
 where [conditional predicate];

You need to use dummy table dual.

In this example, only the second insert-statement will actually insert data into the table:

create table t1(col1 int);
insert into t1(col1) select 1 from dual where 1=0;
insert into t1(col1) select 2 from dual where 1=1;
select * from t1;
+------+
| col1 |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

Answer 4

In an insert statement you wouldn't have an existing row to do a where claues on? You are inserting a new row, did you mean to do an update statment?

update users set username='JACK' and password='123' WHERE id='1';

Answer 5

I think you are looking for UPDATE and not insert?

UPDATE `users`
SET `username` = 'Jack', `password` = '123'
WHERE `id` = 1

Answer 6

To add a WHERE clause inside an INSERT statement simply;

INSERT INTO table_name (column1,column2,column3)
SELECT column1, column2, column3 FROM  table_name
WHERE column1 = 'some_value'