mysql - SQL 将值更新为另一个表中的值

Question

我有三个表，它们都可以通过某些 ID 连接，并且我需要根据其中一个表中已存在的值填充一个新列。

这是架构:

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_attendance_code_group` int(11) DEFAULT '18',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO users (user_attendance_code_group) VALUES 
  (1),
(1),
(3),
(2),
(2),
(2);

CREATE TABLE `attendance_time_index` (
  `time_index_id` int(11) NOT NULL AUTO_INCREMENT,
  `time_index_value` varchar(45) NOT NULL,
  `time_index_user_id` int(11) NOT NULL,
  `time_index_default` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`time_index_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO attendance_time_index (time_index_value, time_index_user_id) VALUES 
  ('AM', 3),
('PM', 3),
  ('AM', 4),
('PM', 4),
  ('AM', 5),
('PM', 5),
  ('AM', 6),
('PM', 6),
  ('AM', 7),
('PM', 7),
  ('AM', 8),
('PM', 8);

CREATE TABLE `attendance_code_groups` (
  `group_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `group_am_default` varchar(3) DEFAULT NULL,
  `group_pm_default` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO attendance_code_groups (group_am_default, group_pm_default) VALUES 
  ('A','B'),
('X','Y'),
  ('1','2');

我需要使用 group_am_default 或 group_pm_default 中的值更新 time_index_default 列，具体取决于 time_index_value< 中的值是否存在(上午或下午)

这个更新查询是什么？

我尝试了以下方法，但子查询返回不止一行...

UPDATE attendance_time_index SET time_index_default = (
SELECT test FROM (SELECT CASE WHEN ati.time_index_value = 'PM' THEN acg.group_pm_default ELSE acg.group_am_default END AS test
FROM attendance_time_index ati
JOIN users u ON ati.time_index_user_id = u.user_id
JOIN attendance_code_groups acg ON u.user_attendance_code_group = acg.group_id) AS x) 

这里有一个 SQLFiddle 如果有帮助的话... http://sqlfiddle.com/#!2/1846e/4

Answer 1

UPDATE attendance_time_index ati 
JOIN users u ON ati.time_index_user_id = u.user_id
JOIN attendance_code_groups acg ON u.user_attendance_code_group = acg.group_id
SET ati.time_index_default = CASE WHEN ati.time_index_value = 'PM' THEN acg.group_pm_default ELSE acg.group_am_default END;