CODEWITHSUNDEEP
mysqlunique-keyunique-indexon-duplicate-key
lickmycode
lickmycode

Reputation: 2079

ON DUPLICATE KEY UPDATE - with condition

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?

Upvotes: 2

Views: 1161

Answers (2)

ʰᵈˑ
ʰᵈˑ

Reputation: 11375

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)

Upvotes: 1

M0rtiis
M0rtiis

Reputation: 3774

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)
      ";

Upvotes: 1

Related Questions