MySQL Batchupdate() with ON DUPLICATE KEY UPDATE

Question

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?

Could you add some code?

Peter Svensson
– Peter Svensson

2013-01-02 15:10:19 +00:00
Commented Jan 2, 2013 at 15:10 — Peter Svensson
– Peter Svensson, Commented Jan 2, 2013 at 15:10

informatik01 · Accepted Answer · 2022-06-01 01:54:13Z

3

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] ... ]

13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statemnt

edited Jun 1, 2022 at 1:54

answered Jan 2, 2013 at 16:55

informatik01

16.5k11 gold badges82 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Batchupdate() with ON DUPLICATE KEY UPDATE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related