MYSQL UPDATE question

[ds111]

I have a table with a PRIMARY KEY.

My UPDATE IGNORE returns affected_rows = 0.

How do I distinguish between

a) nothing changed

b) duplicate primary key

Thanks!

[premiso]

To determine the duplicate primary key just do a simple select query before the update, but you should not be changing the primary key with an update.

 

The nothing changed, well the rows affected means nothing changed. So yea. Either post up the actual update query with 2 or 3 rows of test data so we can see what is going on or hope that someone can understand you

[ds111]

I was trying to avoid using a transaction. I have an invoice form with invoice number as a primary key. When user is modifying an invoice, there are three choices:

1. No changes, user just pressed Save

2. Change invoice number to already existing one.

3. Change anything except invoice number.

I am using UPDATE IGNORE. #3 is OK, but #1 and #2 are indistinguishable.

Of course, I can check duplicate invoice number first, but I am trying to avoid using transactions. (Two users can call up the same invoice for modification).

Again, is it any way to find out (check some mysql or stmt vars) why UPDATE IGNORE failed, because there are no changes or because of duplicate primary key ?

 

[PFMaBiSmAd]

When user is modifying an invoice

2. Change invoice number to already existing one.

 

That should cause your UPDATE query to operate on the record that matches the invoice number they changed to (using the WHERE clause), not to alter the invoice number in the record they started with. Allowing the invoice number in an existing record to be changed makes no sense, it is what identifies what record you are operating on.

 

Short-answer: For an UPDATE query, you should only be putting the invoice number in the WHERE clause, not in the SET term edit: or you should not be allowing the invoice number to be altered at all.