MySQL Insert query doesn’t work with WHERE clause

by

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.

More Related Contents:

  1. You can’t specify target table for update in FROM clause
  2. MySQL query finding values in a comma separated string
  3. Subqueries vs joins
  4. generate days from date range
  5. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  6. MIN/MAX vs ORDER BY and LIMIT
  7. How can I merge two MySQL tables?
  8. Can table columns with a Foreign Key be NULL?
  9. SQL: Find the max record per group [duplicate]
  10. Mysql recursion?
  11. is there a group_concat function in ms-access?
  12. Is there an alternative to TOP in MySQL?
  13. How many rows in a database are TOO MANY?
  14. Why does this SQL code give error 1066 (Not unique table/alias: ‘user’)?
  15. calculate a sum of type time using sql
  16. Is it possible to have an indexed view in MySQL?
  17. mysql – how many columns is too many?
  18. How exactly does using OR in a MySQL statement differ with/without parentheses?
  19. Best database field type for a URL
  20. How to renumber primary index
  21. Query that ignore the spaces
  22. mysql automatically store record creation timestamp
  23. Use A Union Or A Join – What Is Faster [closed]
  24. MySQL – how to front pad zip code with “0”?
  25. Find the number of columns in a table
  26. Mysql, reshape data from long / tall to wide
  27. MySQL: Compare differences between two tables
  28. Mysql strip time component from datetime
  29. How do I start auto increment from a specific point?
  30. How do MySQL views work?

Leave a Comment