MySQL Insert query doesn't work with WHERE clause

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.

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

1. INSERT specifying values

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

2. INSERT using a SELECT statement

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

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.

I do not believe the insert has a WHERE clause.

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...

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.

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)...

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'!='';

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-->

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

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?

You can do that with the below code:

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

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);

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