mysql - 内部加入更新

Question

我正在尝试检查用户是否拥有一门类(class)，我正在尝试通过获取 transaction_id(已申请类(class)的人)然后离开加入类(class)并根据 transaction_course 所在的 session 检查 course_user等于类(class)编号

我的 SQL

UPDATE training_transactions 
       LEFT JOIN training 
              ON training_transactions.training_transaction_id = 
                 training.course_id 
SET    training_transactions.training_transaction_status = 'declined' 
WHERE  training_transactions.training_transaction_id = ? 
       AND training.course_user = ? 

training_transaction:

CREATE TABLE IF NOT EXISTS `training_transactions` (
  `training_transaction_id` int(11) NOT NULL,
  `training_transaction_user` int(11) NOT NULL,
  `training_transaction_course` int(11) NOT NULL,
  `training_transaction_status` varchar(50) NOT NULL,
  `training_transaction_enabled` varchar(50) NOT NULL DEFAULT 'enabled',
  `training_transaction_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

训练

CREATE TABLE IF NOT EXISTS `training` (
  `course_id` int(11) NOT NULL,
  `course_user` int(11) NOT NULL,
  `course_type` varchar(255) NOT NULL,
  `course_name` varchar(255) NOT NULL,
  `course_location` varchar(255) NOT NULL,
  `course_duration` varchar(255) NOT NULL,
  `course_fitness_type` varchar(255) NOT NULL,
  `course_instructor_name` varchar(255) NOT NULL,
  `course_price` int(15) NOT NULL,
  `course_start_date` date NOT NULL,
  `course_max_attendees` int(8) NOT NULL,
  `course_accommodation` varchar(255) NOT NULL,
  `course_accommodation_price` varchar(255) NOT NULL,
  `course_status` varchar(50) NOT NULL,
  `course_enabled` varchar(10) NOT NULL DEFAULT 'enabled'
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;

所以我的问题是，如果交易 ID 与 course_id 匹配并且用户 session (下方)与 course_user 匹配，我该如何更新？

Session::get('id') // user id

Answer 1

您应该在 WHEN 子句中测试 training_transaction_course，而不是 training_transaction_id。你应该使用INNER JOIN，而不是LEFT JOIN；如果您需要在第一个表中获取在第二个表中没有匹配项的行，则只需要 LEFT JOIN。

UPDATE training_transactions AS tt
INNER JOIN training AS t ON tt.training_transaction_id = t.course_id 
SET tt.training_transaction_status = 'declined' 
WHERE tt.training_transaction_course = ? 
   AND t.course_user = ?