How to write insert with where clause?

Question

I have a table1 with only two columns id and password and so i had to insert values with id and password where id not like 'ADMIN%'... I tried my query as below:

insert into table1 values('','') where id not like 'ADMIN%' (doesn't work)
insert into table values('','') select id,password from table1 where id not like 'ADMIN%' (doesn't work)

Both queries didn't work. please help !!

okio@ No to the same table !! I should be able to insert id's with password into a table named table1 except when id=admin — user3481001, Apr 07 '14 at 21:26
Eric @ But i wanted to limit the insertion with a condition when id != admin — user3481001, Apr 07 '14 at 21:27
RTFM: http://dev.mysql.com/doc/refman/5.6/en/insert.html `INSERT` has no `where` clause. — Marc B, Apr 07 '14 at 21:35
i don't have any existing records to update...i wanted to enter new users with id's with a single limitation for ID. Is it possible? — user3481001, Apr 07 '14 at 21:40
Simple: Don't run an `INSERT` statement for things you don't want inserted. — tadman, Apr 07 '14 at 21:46
What is the question? Did you set properties and values for those two columns? — Kanjah77, Apr 07 '14 at 21:25

score 1 · Answer 1 · answered Apr 07 '14 at 21:26

1

Something like

insert into table (id, password) select id,password from table1 where id not like 'ADMIN%'

As per http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

answered Apr 07 '14 at 21:26

Chrisky

567
3
9

score 1 · Answer 2 · edited May 23 '17 at 11:52

1

MYSQL INSERT does not work with the WHERE clause. please check:

MySQL Insert Where query

edited May 23 '17 at 11:52

Community

1
1

answered Apr 07 '14 at 21:38

Clark Superman

373
2
13

score -1 · Accepted Answer · answered Apr 07 '14 at 21:39

-1

insert into table ('id', 'password'); select id,password from table1 where id not like 'ADMIN%'; semicolons...

Try this...

answered Apr 07 '14 at 21:39

Kanjah77

25
6

How to write insert with where clause?

3 Answers3