Unfortunately not. You can get half-way there by not having to repeat the value: INSERT INTO `tableName` (`a`,`b`,`c`) VALUES (1,2,3) ON DUPLICATE KEY UPDATE `a`=VALUES(`a`), `b`=VALUES(`b`), `c`=VALUES(`c`); But you still have to list the columns.
I don’t think there is a way to bypass this behaviour of INSERT … ON DUPLICTE KEY UPDATE. You can however put two statements, one UPDATE and one INSERT, in one transaction: START TRANSACTION ; UPDATE pages SET etc=”randomness” WHERE name=”bob” ; INSERT INTO pages (name, etc) SELECT ‘bob’ AS name , ‘randomness’ AS etc … Read more
You could set the innodb_autoinc_lock_mode config option to “0” for “traditional” auto-increment lock mode, which guarantees that all INSERT statements will assign consecutive values for AUTO_INCREMENT columns. That said, you shouldn’t depend on the auto-increment IDs being consecutive in your application. Their purpose is to provide unique identifiers.
I believe you are looking for the following syntax: INSERT INTO <table> (field1, field2, field3, …) VALUES (‘value1’, ‘value2′,’value3′, …) ON DUPLICATE KEY UPDATE field1=’value1′, field2=’value2′, field3=’value3’, … Note: With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row … Read more
These steps can be used to emulate this functionality: Create a new temporary table. CREATE TEMPORARY TABLE temporary_table LIKE target_table; Optionally, drop all indices from the temporary table to speed things up. SHOW INDEX FROM temporary_table; DROP INDEX `PRIMARY` ON temporary_table; DROP INDEX `some_other_index` ON temporary_table; Load the CSV into the temporary table LOAD DATA … Read more
The UPDATE statement is given so that older fields can be updated to new value. If your older values are the same as your new ones, why would you need to update it in any case? For eg. if your columns a to g are already set as 2 to 8; there would be no … Read more