如果一对匹配,则更新单元格值 [英] UPDATE cell value if a pair matches
问题描述
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
另一个表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' 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
现在,我想要实现的是在我的 ipnstats
表中, used_times
的值将更新 IFF(如果且仅当)两个索引(昵称
和 ipstats_id
)在表中匹配。我的插入/更新命令如下所示:
INSERT INTO`ipstats_nicks`(`ipstats_id`,`nick`,`last_used` )
VALUES(%d,'%s','%s')
ON DUPLICATE KEY
更新`last_used` ='%s',`used_times` =`used_times` + 1
然后我使用变量格式化此字符串。但是这并不表示我所期望的更新。
任何帮助都不胜感激。
有两个问题:
-
ON DUPLICATE KEY UPDATE
仅适用于 UNIQUE 索引。您的索引不唯一。 - 如果任何一个索引发生冲突,它将执行更新。没有办法告诉它只有当两个索引有冲突时才执行更新。
也许你真正想要的是一个唯一的多列索引?
UNIQUE INDEX`ipstats_id_nick`(`ipstats_id`,`nick`)
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.
There are two problems:
ON DUPLICATE KEY UPDATE
only works for UNIQUE indexes. Your indexes are not unique.- 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`)
这篇关于如果一对匹配,则更新单元格值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!