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?

score 1 · Answer 1 · answered Aug 11 '15 at 16:12

U should have an UNIQUE composite key on (log_user_id, log_visitor_id)

ALTER TABLE  `tb_logs` ADD UNIQUE (
    `log_user_id` ,
    `log_visitor_id`
);

and then

$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_date=VALUES(log_date),
          log_counter=log_counter+VALUES(log_counter)
      ";

score 1 · Accepted Answer · answered Aug 11 '15 at 16:14

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)

answered Aug 11 '15 at 16:14

ʰᵈˑ

11,279
3
26
49

What's the sense of `user and visitor id` after UNIQUE INDEX? And what's the meaning of adding USING BTREE at the end? – lickmycode Aug 11 '15 at 16:20
`user and visitor id` is just the name of the index. Using BTREE is used for column comparisons (` =, >, >=, <, <=` etc) https://dev.mysql.com/doc/refman/5.5/en/index-btree-hash.html – ʰᵈˑ Aug 11 '15 at 16:28

ON DUPLICATE KEY UPDATE - with condition

2 Answers2