Upsert a ton of columns with one query

Question

I'm trying to execute the following code on my test server:

Insert into CURRENT values (2, 962, 123.45, 30, 0, '2012-06-08 10:41:23', 100, 80, 
  'Rainy', 120, 50, '2012-06-08 10:14:27', 19300.10, 150.27, 30000, null, 1, 0) 
  ON DUPLICATE KEY UPDATE (WEIGHT, TWEIGHT, OVERRIDE, WEATHER_TS, TEMP, HUMIDITY, 
CONDITIONS, T_HIGH, T_LOW, TRAVELTIME, LOAD, PRICE, AVAILABLE, FREQUENCY, ACTIVE, DIS);

The values 2 and 962 are foreign keys, the '2' is the primary key (ID).

I keep getting an error for the ( before WEIGHT.

What is the actual error? A generic 'syntax error' or something more specific? Is the syntax supported on your version of MySQL? Are you following the syntax diagram correctly? — Jonathan Leffler, Jun 08 '12 at 14:58

score 2 · Answer 1 · answered Jun 08 '12 at 14:58

2

According to the MySQL manual, you aren't quite doing it right. In the UPDATE section, you need to treat it as if it were a regular update query. So it would look something like:

ON DUPLICATE KEY UPDATE WEIGHT=2, TWEIGHT=962...

answered Jun 08 '12 at 14:58

WWW

9,734
1
29
33

score 1 · Accepted Answer · answered Jun 08 '12 at 14:58

1

use it like this

Insert into CURRENT 
values (2, 962, ...) 
ON DUPLICATE KEY UPDATE WEIGHT = 2, TWEIGHT = 962, ...

answered Jun 08 '12 at 14:58

juergen d

201,996
37
293
362

Upsert a ton of columns with one query

2 Answers2