SQL不能更新几行

Question

我的更新查询有问题。如果为action_type = abuse，我只需要将最后一个用户行的notify_admin从0更改为1。(结果应为包含id=9和id=13的行)

我正在尝试这样的东西：

UPDATE user_log SET notify_admin = 1 
WHERE id IN (
  SELECT DISTINCT user_id FROM (SELECT user_id FROM user_log) as UNIKALNE
) AND action_type LIKE 'abuse' 

不幸的是，它只更新了1行(id=3)。

下面是我的表格：

CREATE TABLE IF NOT EXISTS `user_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `action_type` enum('login','logout','abuse') CHARACTER SET latin1 NOT NULL, 
  `notify_admin` tinyint(1) NOT NULL DEFAULT '0', 
  `saved` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15; 

INSERT INTO `user_log` (`id`, `user_id`, `action_type`, `notify_admin`,  `saved`) VALUES
(1, 1, 'login', 0, '2015-11-02 12:13:14'), 
(2, 1, 'logout', 0, '2015-11-02 13:12:11'),
(3, 1, 'abuse', 0, '2016-01-03 14:10:02'), 
(4, 2, 'abuse', 0, '2016-01-04 17:47:03'), 
(5, 2, 'login', 0, '2016-01-04 18:11:55'), 
(6, 1, 'abuse', 0, '2016-01-04 18:23:57'), 
(7, 1, 'abuse', 0, '2016-01-04 18:24:23'), 
(8, 2, 'logout', 0, '2016-01-04 18:25:24'),
(9, 1, 'abuse', 0, '2016-01-04 18:25:32'), 
(10, 1, 'login', 0, '2016-01-05 21:02:59'), 
(11, 3, 'login', 0, '2016-01-05 21:28:43'), 
(12, 3, 'logout', 0, '2016-01-05 21:52:01'),
(13, 2, 'abuse', 0, '2016-01-05 22:00:35'), 
(14, 1, 'logout', 0, '2016-01-05 22:12:09'); 

Answer 1

您需要首先获取每个用户的最新saved值，然后更新该列。

UPDATE user_log 
JOIN 
(
  select id from user_log JOIN (
    select user_id,max(saved) max_saved
    from user_log
    where action_type="abuse"
    group by user_id
  ) t
  ON t.user_id = user_log.user_id AND t.max_saved = user_log.saved
) t2
ON user_log.id = t2.id
SET notify_admin = 1

Answer 2

在这里，您使用distinct检查表中的User_id，因此它将只给出1,2,3，然后与滥用操作类型进行比较，因此它将更新匹配的第3行。

如果要更新所有行，请将User_id替换为id

UPDATE user_log SET notify_admin = 1 WHERE id IN (SELECT DISTINCT id FROM (SELECT id FROM user_log) as UNIKALNE) AND action_type LIKE 'abuse'