I am using batchUpdate() to insert multiple records in a table. As per the requirement, if there is a duplicate value for a primary key while inserting it should be updated with the latest received data, hence I was trying to use ON DUPLICATE KEY UPDATE in INSERT statement. The ON DUPLICATE KEY UPDATE works good when I use a single insert, but it gives error when I use batchupdate() for same query. The Error is as follows:
Testcase: testVehicleTracking(com.em.ert.test.TrackingServiceTest): Caused an ERROR PreparedStatementCallback; bad SQL grammar [INSERT INTO test_tracking (sino, material_id, material_type, vehicle_position, rundown_num, msg_type, msg_status, msg_timestamp) VALUES(?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE]; nested exception is java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
I am not getting why ON DUPLICATE KEY UPDATE does not work with batchupdate(). Ideally it should since there is nothing different in the working as far as I see.
What should I be doing for achieving this?
You are missing column assignment col_name = value after ON DUPLICATE KEY UPDATE clause.
From the official MySQL reference manual:
13.2.6 INSERT Statement
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]