UPDATE cell value if a pair matches

Question

I am using luasql. I have two tables of this type:

IPINFO

CREATE TABLE `ipstats` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(15) NOT NULL,
  `last_used` DATETIME NOT NULL DEFAULT '1981-09-30 00:00:00',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `ip` (`ip`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

and another table ipnstats:

CREATE TABLE `ipnstats` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ipstats_id` INT(10) UNSIGNED NOT NULL,
  `nick` VARCHAR(32) NOT NULL,
  `used_times` INT(10) UNSIGNED NOT NULL,
  `last_used` DATETIME NOT NULL DEFAULT '1981-09-30 00:00:00',
  PRIMARY KEY (`id`),
  INDEX `ipstats_id` (`ipstats_id`),
  INDEX `nick` (`nick`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT

Now, what I am trying to achieve here is that in my ipnstats table, the value of used_times will be updated IFF(if and only if) both the indexes(nickname and ipstats_id) in the table are matched. My insertion/updation command goes like this:

INSERT INTO `ipstats_nicks` (`ipstats_id`, `nick`, `last_used`) 
  VALUES ( %d, '%s', '%s' ) 
  ON DUPLICATE KEY 
  UPDATE `last_used` = '%s', `used_times` = `used_times`+1

and then I am formatting this string using variables. But this is not giving me the desired update in table. It is just keeping on inserting data into the table.

Any help is appreciated.

Antiparallel

Answer 1

There are two problems:

1. ON DUPLICATE KEY UPDATE only works for UNIQUE indexes. Your indexes are not unique.
2. If any single index gives a conflict, it will perform an update. There's no way to tell it to only perform an update when both indexes have a conflict.

Perhaps what you really want is a single unique multi-column index?

UNIQUE INDEX `ipstats_id_nick` (`ipstats_id`, `nick`)

Corrasion

but it is not updating at all. (: – Antiparallel

@T-ShirtDude: Yes, of course it doesn't update. That's explained by point 1. Your indexes are not UNIQUE. Did I not make that point bold enough for you? – Corrasion

Yeah! Thanks. :) Didn't know that was possible. :) – Antiparallel