Mysql Duplicate entry 'xxxxxxxx' for key (unique) 'xxxxxxxxxx'
I have a row update problem. I have a column named serialNum
withvarchar(50) not null unique default null
When I receive response data from a partner company, I update the row to match the unique one serial_num
(our company serial series).
Sometimes the update failed due to:
Duplicate entry 'xxxxxxxx' for key 'serialNum'
But the value to update doesn't exist when I go through the whole table. This happens sometimes, not always, about 10 times out of 300.
Why is this happening and how can I solve it?
below is the query I'm using to update:
String updateQuery = "update phone set serialNum=?, Order_state=?, Balance=? where Serial_num=" + resultSet.get("jno_cli");
PreparedStatement presta = con.prepareStatement(updateQuery);
presta.setString(1, resultSet.get("oid_goodsorder"));
presta.setString(2, "order success");
presta.setFloat(3, Float.valueOf(resultSet.get("leftmoney")));
presta.executeUpdate();
source to share
I guess the reason is resultSet.get("oid_goodsorder")
where did you get this result? is 'oid_goodsorder' unique? Have you always updated the whole table?
If the oid_goodsorder is unique, there may be duplicates in serialNum
, because you are not using bulk update, instead you update each record separately, so this is possible:
Before:
serialNum = 11,22,33,44 oid_goodsorder = 44,11,22,33
It tries to upgrade serialNum
to 44 first , but 44 exists!
But if you finish the whole update serialNum
will be unique ...
- If you want to get error rows that you can turn off, the set is
serialNum
not unique, but a checklist to duplicateserialNum
- If you don't have duplicate values, try using Java Bulk Update - How to Insert and Update Inserts to a Database
source to share