3

I have a problem of updating a row. I have a column called serialNum with varchar(50) not null unique default null

When I get the response data from the partner company, i will update the row according to the unique serial_num (our company's serial num).

Sometimes update failed because of :

Duplicate entry 'xxxxxxxx' for key  'serialNum'

But the value to update is not exists when i search the whole table. It happens sometimes, not always, like about 10 times out of 300.

Why does this happen and how can I solve it?

below is the query i use 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();
Will
  • 633
  • 11
  • 26
  • 2
    MySQL is case insensitive by default. Could that be why it is finding duplicates? – khelwood May 22 '15 at 09:29
  • Also beware of leading/trailing whitespaces –  May 22 '15 at 09:30
  • 1
    Show us the complete queries you are using to update and search. – Tagir Valeev May 22 '15 at 09:31
  • Are you creating the querry yourself or are you using a library like hibernate to do so? – qwerty May 22 '15 at 09:36
  • this project, i did not use any framework. i create the query myself – Will May 22 '15 at 09:41
  • @Tagir Valeev i added the update query in the question. for the search, i use "select * from phone where serialNum='xxxxxxxx'" in the mysql workbench, and it returns 0 row(s) – Will May 22 '15 at 09:47
  • 3
    Are there two records with the same "Serial_num"? If there are, the update would end up setting the "serialNum" of those two records to the same value, breaking the constraint. By the way, having one column called Serial_num and another called serialNum sounds like a disaster waiting to happen. – Joni May 22 '15 at 10:09
  • @Joni the Serial_num is unique as well – Will May 22 '15 at 10:15
  • can you `show create table phone`, perhaps there are constraints we don't know of? – geert3 May 22 '15 at 10:18
  • Can you share phone table structure and and records in the database in the moment of update executing following query: "select * from phone where Serial_num=" + resultSet.get("jno_cli") – gandra404 May 22 '15 at 12:06

1 Answers1

0

I think the reason is in resultSet.get("oid_goodsorder") where did you get this result? is 'oid_goodsorder' is unique? Did you always updates whole table?
If oid_goodsorder is unique, it is possible to have duplicates in serialNum, because you don't use bulk update, instead you update every record separately, therefore it is possible:

Before:
serialNum=11,22,33,44 oid_goodsorder=44,11,22,33

It tries to update first serialNum to 44, but 44 is exists!
But if you finish all update serialNum will be unique...

  1. If you wants to get error rows you could disable set serialNum is not unique and check table for duplicating serialNum
  2. If you don't have duplicating values try to use bulk update Java - how to batch database inserts and updates
Community
  • 1
  • 1
rpc1
  • 688
  • 10
  • 23