ON DUPLICATE UPDATE

Question

There are several threads about this on SO and other forums. But, I didn't clear about few things when I would like to do a insert query in MySQL. I found a thread which is interesting and quite similar question. But, I would like hear a good suggestions from you.

1. ON DUPLICATE KEY UPDATE- Is this only refer the primary key or other columns of table?
2. If other then I have another questions. Suppose, I have a table something like following columns-

log_id (PRIMARY KEY, user_id (FOREIGN KEY), working_date, time_in, time_out

Scenario:

1. On 2014-05-04, Mr. Bin (user_id: 2) checkin at office at 09:03:12 AM
2. On 2014-05-04, Mr. Bin (user_id: 2) checkout at office at 03:13:12 PM

At this point, I wanna to update the existing row which already created at morning for user_id: 2 on same date. I just want to add the checkout time.

Which I already tried:

$sql = "INSERT INTO work_log (user_id, working_date, time_out) 
VALUES('2', '2014-05-04', '03:13:12')
ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), working_date = VALUES(working_date)";

Answer 1

Define a composite key like:

UNIQUE KEY( user_id, working_date )

And in the ON DUPLICATE, set values like:

ON DUPLICATE KEY 
    UPDATE time_out = VALUES(time_out);