ON DUPLICATE KEY UPDATE - with condition

Question

Haven't worked a lot with INSERT INTO ... ON DUPLICATE KEY UPDATE queries yet, so please lead me to a solution.

The database table is named 'tb_logs' with 4 columns:

log_user_id, log_visitor_id, log_date, log_counter

I want to insert data, and only update the log_date and log_counter, if a row log_user_id AND log_visitor_id already exists with the exact same values which I'm going to insert.

Whatever I've tried, can't get it working, it updates rows which shouldn't be updated, or adding new rows, which shouldn't be added.

My recent code:

$sql = "
        INSERT INTO tb_logs (
          log_user_id,
          log_visitor_id,
          log_date,
          log_counter
        ) VALUES (
          '{$user}',
          '{$visitor}', 
          UNIX_TIMESTAMP(CURRENT_TIMESTAMP),
          '1'
        ) ON DUPLICATE KEY UPDATE
          log_user_id='{$user}',
          log_visitor_id='{$visitor}',
          log_date=UNIX_TIMESTAMP(CURRENT_TIMESTAMP),
          log_counter=log_counter+1
      "; 

Perhaps I just haven't understood the ON DUPLICATE KEY UPDATE query correctly?

Example:

log_user_id   log_visitor_id   log_date   log_counter
--------------------------------------------------------
     1              5          23434234        1

When now log_user_id=1 AND log_visitor_id=5 again, then don't insert a new row and just update log_date and log_counter

Is this possible?

Answer 1

Yes, first create a unique index.

ALTER TABLE `tb_logs`
ADD UNIQUE INDEX `user and visitor id` (`log_user_id`, `log_visitor_id`) USING BTREE;

Then structure your query as you've done;

INSERT INTO tb_logs (
    log_user_id,
    log_visitor_id,
    log_date,
    log_counter
)
VALUES
    ('{$user}',
      '{$visitor}', 
      UNIX_TIMESTAMP(CURRENT_TIMESTAMP),
      '1')
 ON DUPLICATE KEY
UPDATE log_counter = log_counter + 1, log_date = UNIX_TIMESTAMP(CURRENT_TIMESTAMP)