mysql - 根据条件更新表

Question

我必须从一个设置的表中获取类型（“student”，“staff'”），根据结果我必须更新staff或student_class_attendance表我试试这个

SELECT TYPE  
CASE    WHEN TYPE = 'staff' THEN
        UPDATE  staff_attendance SET statues = 1 WHERE staff_id = (SELECT type_id FROM rfid_details WHERE rfid_no = '124' )     
WHEN TYPE = 'student' THEN
    UPDATE  student_class_attendance SET statues = 1 WHERE student_id = (SELECT type_id FROM rfid_details WHERE rfid_no = '124' )
END 
FROM rfid_details WHERE rfid_no = '124'

并且

SELECT TYPE  IF(TYPE = staff, 
UPDATE staff_attendance 
SET    statues = 1 
WHERE  staff_id = 

          SELECT type_id 
          FROM   rfid_details 
          WHERE  rfid_no = '124' ,UPDATE student_class_attendance 
SET    statues = 1 
WHERE  student_id= 

          SELECT type_id 
          FROM   rfid_details 
          WHERE  rfid_no = '124' ) FROM rfid_details WHERE rfid_no = '124'

但显示

您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，以在第 2 行的“CASE WHEN TYPE = 'staff' THEN UPDATE staff_attendance SET Stats = 1 WHER”附近使用正确的语法

如果

您的 SQL 语法有错误；检查与您的 MariaDB 服务器版本相对应的手册，以在第 1 行的 'IF(type = staff, update staff_attendance SET states = 1 WHERE staff_i' 附近使用正确的语法

请帮助解决这个问题

标签： mysql

Answer 1

您根本无法在 select 语句中进行更新。您可以尝试以下方法：

UPDATE  staff_attendance SET statues = 1 WHERE staff_id = (SELECT type_id FROM rfid_details WHERE rfid_no = '124' and type = 'staff');
UPDATE  student_class_attendance SET statues = 1 WHERE student_id = (SELECT type_id FROM rfid_details WHERE rfid_no = '124' and type = 'student');