mysql - 目标表不可更新

Question

我有以下查询

 UPDATE (SELECT user_id,method_id FROM User AS U
                JOIN Address AS A 
                  ON A.user_id=U.id
                JOIN Shipping AS S 
                  ON S.address_id= A.id  WHERE user_id=1) AS T
 SET T.method_id=5;    

我收到错误消息“更新的目标表 T 不可更新”。如何更改我的查询以更新子查询中的列？任何解释都会很棒我正在使用 MySQL

Answer 1

UPDATE Shipping s
JOIN Address AS a ON s.address_id = a.id
SET s.method_id = 5
WHERE a.user_id = 1

不需要加入User 表，因为user_id 在Address 表中。如果确实需要在更新前检查用户是否存在，可以是:

UPDATE Shipping s
JOIN Address AS a ON s.address_id = a.id
JOIN User AS u ON a.user_id = u.id
SET s.method_id = 5
WHERE u.id = 1