MySql Table Insert if not exist otherwise update

by

Jai is correct that you should use INSERT ... ON DUPLICATE KEY UPDATE.

Note that you do not need to include datenum in the update clause since it’s the unique key, so it should not change. You do need to include all of the other columns from your table. You can use the VALUES() function to make sure the proper values are used when updating the other columns.

Here is your update re-written using the proper INSERT ... ON DUPLICATE KEY UPDATE syntax for MySQL:

INSERT INTO AggregatedData (datenum,Timestamp)
VALUES ("734152.979166667","2010-01-14 23:30:00.000")
ON DUPLICATE KEY UPDATE 
  Timestamp=VALUES(Timestamp)

More Related Contents:

  1. “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”
  2. MySQL Conditional Insert
  3. How to do a batch insert in MySQL
  4. Get the new record primary key ID from MySQL insert query?
  5. Which is faster: multiple single INSERTs or one multiple-row INSERT?
  6. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  7. Prevent auto increment on MySQL duplicate insert
  8. MySQL trigger On Insert/Update events
  9. How to copy a row and insert in same table with a autoincrement field in MySQL?
  10. MySQL Error 1264: out of range value for column
  11. BULK INSERT in MYSQL
  12. How do I get the ID of multiple inserted rows in MySQL?
  13. MySQL trigger to update a field to the value of id
  14. How to insert data to MySQL with auto-incremented column(field)?
  15. How to insert values in table with foreign key using MySQL?
  16. MySQL INSERT INTO … VALUES and SELECT
  17. MySQL error 1241: Operand should contain 1 column(s)
  18. MySQL Insert & Joins
  19. How to add a where clause in a MySQL Insert statement?
  20. Reserved word in column name – insert into MySQL [duplicate]
  21. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  22. MySQL LAST_INSERT_ID() used with multiple records INSERT statement
  23. Mysql insert into 2 tables
  24. MySQL – insert data from another table merged with constants
  25. Insert data into table with result from another select query
  26. MySQL optimizing INSERT speed being slowed down because of indices
  27. MySQL: Insert datetime into other datetime field
  28. “Unknown column in ‘field list'”, but column does exist
  29. Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?
  30. Insert and set value with max()+1 problems

Leave a Comment