MySQL Insert query doesn't work with WHERE clause

Question

What's wrong with this query:

INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;

It works without the WHERE clause. I've seemed to have forgot my SQL.

score 280 · Accepted Answer · edited Aug 08 '12 at 20:15

MySQL INSERT Syntax does not support the WHERE clause so your query as it stands will fail. Assuming your id column is unique or primary key:

If you're trying to insert a new row with ID 1 you should be using:

INSERT INTO Users(id, weight, desiredWeight) VALUES(1, 160, 145);

If you're trying to change the weight/desiredWeight values for an existing row with ID 1 you should be using:

UPDATE Users SET weight = 160, desiredWeight = 145 WHERE id = 1;

If you want you can also use INSERT .. ON DUPLICATE KEY syntax like so:

INSERT INTO Users (id, weight, desiredWeight) VALUES(1, 160, 145) ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145

OR even like so:

INSERT INTO Users SET id=1, weight=160, desiredWeight=145 ON DUPLICATE KEY UPDATE weight=160, desiredWeight=145

It's also important to note that if your id column is an autoincrement column then you might as well omit it from your INSERT all together and let mysql increment it as normal.

Thanks for a good answer. When using UPDATE query its working fine. But, when id is a primary key & auto increment key then the above INSERT statement is not work.Error Duplicate entry '1' for key 1. — JDGuide, Aug 06 '12 at 06:26
@JDeveloper That is because the row already exists. As Chad mentioned `If you're trying to insert a new row with ID 1 you should be using` INSERT INTO. In your case you're trying to insert when ID 1 already exists and it's a primary or unique key and fails so you should either use UPDATE syntax or INSERT .. ON DUPLICATE KEY syntax — Anthony Hatzopoulos, Aug 08 '12 at 20:12

Russ Cam · Answer 2 · 2013-08-02T10:37:18.740

50

You can't combine a WHERE clause with a VALUES clause. You have two options as far as I am aware-

INSERT specifying values

INSERT INTO Users(weight, desiredWeight) 
VALUES (160,145)

INSERT using a SELECT statement

INSERT INTO Users(weight, desiredWeight) 
SELECT weight, desiredWeight 
FROM AnotherTable 
WHERE id = 1

edited Aug 02 '13 at 10:37

answered Jan 27 '09 at 20:21

Russ Cam

124,184
33
204
266

score 24 · Answer 3 · edited Jul 12 '19 at 14:40

You use the WHERE clause for UPDATE queries. When you INSERT, you are assuming that the row doesn't exist.

The OP's statement would then become;

UPDATE Users SET weight = 160, desiredWeight = 45 where id = 1;

~~In MySQL, if you want to INSERT or UPDATE, you can use the REPLACE query with a WHERE clause. If the WHERE doesn't exist, it INSERTS, otherwise it UPDATES.~~

EDIT

I think that Bill Karwin's point is important enough to pull up out of the comments and make it very obvious. Thanks Bill, it has been too long since I have worked with MySQL, I remembered that I had issues with REPLACE, but I forgot what they were. I should have looked it up.

That's not how MySQL's REPLACE works. It does a DELETE (which may be a no-op if the row does not exist), followed by an INSERT. Think of the consequences vis. triggers and foreign key dependencies. Instead, use INSERT...ON DUPLICATE KEY UPDATE.

That's not how MySQL's REPLACE works. It does a DELETE (which may be a no-op if the row does not exist), followed by an INSERT. Think of the consequences vis. triggers and foreign key dependencies. Instead, use INSERT...ON DUPLICATE KEY UPDATE. — Bill Karwin, Jan 27 '09 at 21:30

score 13 · Answer 4 · answered Jan 27 '09 at 20:17

13

I do not believe the insert has a WHERE clause.

answered Jan 27 '09 at 20:17

Daniel A. White

187,200
47
362
445

That is true and sand in the same time! – Ingus Jun 14 '17 at 14:03

score 7 · Answer 5 · edited Jun 20 '20 at 09:12

Insert query doesn't support where keyword*

Conditions apply because you can use where condition for sub-select statements. You can perform complicated inserts using sub-selects.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';

By placing a "select" in the insert statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQL statement before performing the insert.

SELECT count(*)
FROM customers
WHERE city = 'Newark';

You can make sure that you do not insert duplicate information by using the EXISTS condition.

For example, if you had a table named clients with a primary key of client_id, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

This statement inserts multiple records with a subselect.

If you wanted to insert a single record, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

See also How to insert with where clause

score 5 · Answer 6 · answered Jul 06 '11 at 10:38

The right answer to this question will be sth like this:

a). IF want select before insert :

INSERT INTO Users( weight, desiredWeight ) 
  select val1 , val2  from tableXShoulatNotBeUsers
  WHERE somecondition;

b). IF record already exists use update instead of insert:

 INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;

Should be

Update Users set weight=160, desiredWeight=145  WHERE id = 1;

c). If you want to update or insert at the same time

Replace Users set weight=160, desiredWeight=145  WHERE id = 1;

Note):- you should provide values to all fields else missed field in query 
        will be set to null

d). If you want to CLONE a record from SAME table, just remember you cann't select from table to which you are inserting therefore

 create temporary table xtable ( weight int(11), desiredWeight int(11) ;

 insert into xtable (weight, desiredWeight) 
    select weight, desiredWeight from Users where [condition]

 insert into Users (weight, desiredWeight) 
    select weight , desiredWeight from xtable;

I think this pretty covers most of the scenarios

score 4 · Answer 7 · answered Aug 09 '12 at 12:43

You simply cannot use WHERE when doing an INSERT statement:

 INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;

should be:

 INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 );

The WHERE part only works in SELECT statements:

SELECT from Users WHERE id = 1;

or in UPDATE statements:

UPDATE Users set (weight = 160, desiredWeight = 145) WHERE id = 1;

score 4 · Answer 8 · answered Feb 21 '19 at 08:35

4

A way to use INSERT and WHERE is

INSERT INTO MYTABLE SELECT 953,'Hello',43 WHERE 0 in (SELECT count(*) FROM MYTABLE WHERE myID=953); In this case ist like an exist-test. There is no exception if you run it two or more times...

answered Feb 21 '19 at 08:35

Rob

41
1

1

This should be accepted as the answer! Works perfectly! – userM1433372 Jun 20 '21 at 17:34

score 3 · Answer 9 · answered Oct 01 '19 at 01:06

I think that the correct form to insert a value on a specify row is:

UPDATE table SET column = value WHERE columnid = 1

it works, and is similar if you write on Microsoft SQL Server

INSERT INTO table(column) VALUES (130) WHERE id = 1;

on mysql you have to Update the table.

Igor Vujovic · Answer 10 · 2015-06-16T12:23:05.603

2

You can do conditional INSERT based on user input. This query will do insert only if input vars '$userWeight' and '$userDesiredWeight' are not blank

INSERT INTO Users(weight, desiredWeight )
select '$userWeight', '$userDesiredWeight'  
FROM (select 1 a ) dummy
WHERE '$userWeight' != '' AND '$userDesiredWeight'!='';

edited Jun 16 '15 at 12:23

answered Nov 21 '13 at 09:19

Igor Vujovic

419
6
8

score 2 · Answer 11 · answered Jan 27 '09 at 20:20

2

Insert into = Adding rows to a table

Upate = update specific rows.

What would the where clause describe in your insert? It doesn't have anything to match, the row doesn't exist (yet)...

answered Jan 27 '09 at 20:20

Richard L

1,211
7
10

score 2 · Answer 12 · answered Mar 05 '22 at 16:59

2

If you are looking to insert some values into a new column of an altered table in each rows by mentioning its primary key, then just-->

UPDATE <table_name> SET <column_name> = '<value> WHERE <primary_key> = <primary_value>

answered Mar 05 '22 at 16:59

Rjraj

51
4

This answer doesn't add new information compared to the [accepted answer from 2009](https://stackoverflow.com/a/485062/12345551). – He3lixxx Mar 11 '22 at 19:56

JDGuide · Answer 13 · 2012-08-07T05:28:17.607

1

If you are specifying a particular record no for inserting data its better to use UPDATE statement instead of INSERT statement.

This type of query you have written in the question is like a dummy query.

Your Query is :-

INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;

Here , you are specifying the id=1 , so better you use UPDATE statement to update the existing record.It is not recommended to use WHERE clause in case of INSERT.You should use UPDATE .

Now Using Update Query :-

UPDATE Users SET weight=160,desiredWeight=145 WHERE id=1;

edited Aug 07 '12 at 05:28

answered Aug 06 '12 at 06:32

JDGuide

6,239
12
46
64

No mate.1st query will not work.1st query is the question asked. – JDGuide Aug 07 '12 at 04:15
Why you are suggesting update over insert? User wants to insert values with condition and not update exiting records. – Somnath Muluk Aug 07 '12 at 05:18

score 1 · Answer 14 · answered Aug 07 '12 at 19:37

Does WHERE-clause can be actually used with INSERT-INTO-VALUES in any case?

The answer is definitively no.

Adding a WHERE clause after INSERT INTO ... VALUES ... is just invalid SQL, and will not parse.

The error returned by MySQL is:

mysql> INSERT INTO Users( weight, desiredWeight ) VALUES ( 160, 145 ) WHERE id = 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 1' at line 1

The most important part of the error message is

... syntax to use near 'WHERE id = 1' ...

which shows the specific part the parser did not expect to find here: the WHERE clause.

score 1 · Answer 15 · answered Aug 10 '12 at 10:39

1

its totall wrong. INSERT QUERY does not have a WHERE clause, Only UPDATE QUERY has it. If you want to add data Where id = 1 then your Query will be

UPDATE Users SET weight=160, desiredWeight= 145 WHERE id = 1;

answered Aug 10 '12 at 10:39

Asmarah

134
8

score 1 · Answer 16 · answered Aug 11 '12 at 05:14

1

No. As far as I am aware you cannot add the WHERE clause into this query. Maybe I've forgotten my SQL too, because I am not really sure why you need it anyway.

answered Aug 11 '12 at 05:14

Ikechi Anyanwu

59
1
5

score 1 · Answer 17 · answered Aug 11 '12 at 06:01

You Should not use where condition in Insert statement. If you want to do, use insert in a update statement and then update a existing record.

Actually can i know why you need a where clause in Insert statement??

Maybe based on the reason I might suggest you a better option.

score 1 · Answer 18 · answered Aug 11 '12 at 08:03

1

I think your best option is use REPLACE instead INSERT

REPLACE INTO Users(id, weight, desiredWeight) VALUES(1, 160, 145);

answered Aug 11 '12 at 08:03

Alberto León

2,879
2
25
24

score 1 · Answer 19 · answered Aug 11 '12 at 08:48

DO READ THIS AS WELL

It doesn't make sense... even literally

INSERT means add a new row and when you say WHERE you define which row are you talking about in the SQL.

So adding a new row is not possible with a condition on an existing row.

You have to choose from the following:

A. Use UPDATE instead of INSERT

B. Use INSERT and remove WHERE clause ( I am just saying it...) or if you are real bound to use INSERT and WHERE in a single statement it can be done only via INSERT..SELECT clause...

INSERT INTO Users( weight, desiredWeight ) 
SELECT FROM Users WHERE id = 1;

But this serves an entirely different purpose and if you have defined id as Primary Key this insert will be failure, otherwise a new row will be inserted with id = 1.

yes your are correct , knowing all these things wake make mistakes. — Asesha George, May 25 '16 at 06:08

score 1 · Answer 20 · answered Apr 21 '17 at 15:19

I am aware that this is a old post but I hope that this will still help somebody, with what I hope is a simple example:

background:

I had a many to many case: the same user is listed multiple times with multiple values and I wanted to Create a new record, hence UPDATE wouldn't make sense in my case and I needed to address a particular user just like I would do using a WHERE clause.

INSERT into MyTable(aUser,aCar)
value(User123,Mini)

By using this construct you actually target a specific user (user123,who has other records) so you don't really need a where clause, I reckon.

the output could be:

aUser   aCar
user123 mini
user123 HisOtherCarThatWasThereBefore

score 1 · Answer 21 · answered Jan 15 '11 at 20:23

1

It depends on the situation INSERT can actually have a where clause.

For example if you are matching values from a form.

Consider INSERT INTO Users(name,email,weight, desiredWeight) VALUES (fred,bb@yy.com,160,145) WHERE name != fred AND email != bb@yy.com

Makes sense doesn't it?

answered Jan 15 '11 at 20:23

Frank Nwoko

3,816
5
24
33

is it possible where clause in insert statement – Taja_100 Mar 29 '17 at 05:21

score 1 · Answer 22 · answered Dec 06 '11 at 22:42

1

The simplest way is to use IF to violate your a key constraint. This only works for INSERT IGNORE but will allow you to use constraint in a INSERT.

INSERT INTO Test (id, name) VALUES (IF(1!=0,NULL,1),'Test');

answered Dec 06 '11 at 22:42

Ethan Brooks

11
1

score 1 · Answer 23 · edited Jan 14 '12 at 06:55

1

After WHERE clause you put a condition, and it is used for either fetching data or for updating a row. When you are inserting data, it is assumed that the row does not exist.

So, the question is, is there any row whose id is 1? if so, use MySQL UPDATE, else use MySQL INSERT.

edited Jan 14 '12 at 06:55

Udo Held

12,314
11
67
93

answered Jan 14 '12 at 06:51

Revathi

21
1

score 0 · Answer 24 · edited Apr 27 '12 at 17:10

0

correct syntax for mysql insert into statement using post method is:

$sql="insert into ttable(username,password) values('$_POST[username]','$_POST[password]')";

edited Apr 27 '12 at 17:10

antyrat

27,479
9
75
76

answered Apr 27 '12 at 16:43

chaitanya koripella

9
1

1

You need to escape untrusted parameters such as the username or use parameterized queries to prevent an attacker from [injecting arbitrary SQL commands](http://en.wikipedia.org/wiki/SQL_injection). And use one of the HashCrypt algorithms to store the password. – Hendrik Brummermann Aug 11 '12 at 10:17

score 0 · Answer 25 · answered Aug 04 '12 at 12:00

0

i dont think that we can use where clause in insert statement

answered Aug 04 '12 at 12:00

Nipun Jain

626
4
6

score 0 · Answer 26 · edited May 05 '16 at 22:15

0

INSERT INTO Users(weight, desiredWeight )
SELECT '$userWeight', '$userDesiredWeight'  
FROM (select 1 a ) dummy
WHERE '$userWeight' != '' AND '$userDesiredWeight'!='';

edited May 05 '16 at 22:15

Eddwin Paz

2,842
4
28
48

answered May 05 '16 at 21:20

user6297694

1

1

Can you edit your proposed answer to expand on what this does and how it addresses the OP? – Ro Yo Mi May 06 '16 at 04:06

score 0 · Answer 27 · answered Sep 05 '17 at 06:00

0

You can't use INSERT and WHERE together. You can use UPDATE clause for add value to particular column in particular field like below code;

UPDATE Users
SET weight='160',desiredWeight ='145'  
WHERE id =1

answered Sep 05 '17 at 06:00

Vishal Vaishnav

3,346
3
26
57

score 0 · Answer 28 · answered May 30 '20 at 04:11

0

You can do that with the below code:

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition

answered May 30 '20 at 04:11

KimchiMan

4,836
6
35
41

score 0 · Answer 29 · edited Feb 04 '21 at 08:05

0

I think you should do it like this, if you want to validate table not to use email twice

Code :

INSERT INTO tablename(fullname,email) 

SELECT * FROM (SELECT 'fullnameValue' AS fullname_field,'emailValue' AS email_field) entry WHERE entry.email_field NOT IN (SELECT email FROM tablename);

edited Feb 04 '21 at 08:05

fartem

2,361
2
8
20

answered Feb 04 '21 at 02:26

EDman

1
5

score 0 · Answer 30 · answered Jan 27 '22 at 20:45

All the above answers give you the plain MySQL statements.

If you are using the where condition in PHPMyAdmin to update the existing row of a table, below is the suggestion to use.

UPDATE `Table_Name` SET `row1`='[value-1]',`row2`='[value-2]',`row3`='[value-3]' WHERE 1

You can include any value between '' of each row. Not necessarily to put [] to indicate the value. Do not change anything after where. Simply click go after giving the value to update.

Example:

UPDATE `Mytable_name` SET `abc`='xyz',`def`='uvw'
 WHERE 1

score 0 · Answer 31 · answered Oct 10 '22 at 12:46

0

For example I'm inserting cities in a state that i know is having a id 54 from a temporary table tmp_city

the below code would work fine -

insert into tb_cities (state_id, city_nm) (select '54' , t.city_name from tmp_city t)

answered Oct 10 '22 at 12:46

Pradyut Bhattacharya

5,440
13
53
83

MySQL Insert query doesn't work with WHERE clause

31 Answers31

Insert query doesn't support where keyword*

DO READ THIS AS WELL

Linked

Related