CODEWITHSUNDEEP
mysqlinsertsql-updateon-duplicate-key
Mohideen bin Mohammed
Mohideen bin Mohammed

Reputation: 20137

MYSQL: ON DUPLICATE KEY UPDATE Not working?

Here is my SQL query, below insert or update returns 0 row affected.. where i made mistake?

insert into biometric_data.daily_attendance (emp_id, in_punch, out_punch, processeddate_d, worktime)
    values ('A744', '27/12/2019 09:08:31', '27/12/2019 18:56:49', '2019-12-27', '8:48')
    on duplicate key update emp_id = 'A744', processeddate_d = '2019-12-27';

wheni say,

select *
from attendance
where emp_id = 'A744' and processeddate_d = '2019-12-27';

returns 1 row. 

A744 11:30 null 2019-12-27 8:48

but update not working.

Note: gone through many post and tried everything still i dont find where i made mistakes.

my PK is id an auto field and index-> emp_id, processeddate_d unique-> emp_id, processeddate_d

Upvotes: 0

Views: 928

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Ummmm . . . how would you know if the update is working or not?

Your unique key is on (emp_id, processeddate_d). Those are the only two fields that you are updating in the on duplicate key clause:

on duplicate key update emp_id = 'A744', processeddate_d = '2019-12-27';

By definition, these are no-ops, because those are the values that are duplicates. Perhaps there is another column that you intend to update but are not.

Upvotes: 1

Related Questions