mysql - 如果其他表没有相关数据，则更新多个表

Question

我有 4 张 table 。

我想更新我正在使用联接的所有表。

仅当所有表都有值时查询才有效。如果任何一个表没有该记录，则它不会更新其他表

我的查询是

update post_message_users
LEFT JOIN post_messages 
ON post_message_users.messageid = post_messages.messageid 
LEFT JOIN comments
ON post_message_users.messageid = comments.comment_on 
LEFT JOIN likes
ON post_message_users.messageid =likes.element_id 
SET post_message_users.status='deleted',
post_message_users.deleted_time=NOW(),
post_messages.status = 'deleted' ,
post_messages.delete_time = now(),
comments.status ='deleted', 
likes.status='deleted',
likes.delete_time=now()
WHERE 
 comments.element_type ='Message' 
AND
likes.element_type ='Message' 
and post_message_users.messageid = 33 
and post_message_users.received_by= 3

请看看我应该做什么改变才能正常工作。

Answer 1

您的 where 子句正在撤消左外连接，将其转变为内连接。要解决此问题，请将条件移至 on 子句中:

update post_message_users
       LEFT JOIN post_messages 
                 ON post_message_users.messageid = post_messages.messageid 
       LEFT JOIN comments
                 ON post_message_users.messageid = comments.comment_on and
                    comments.element_type = 'Message'
       LEFT JOIN likes
                 ON post_message_users.messageid = likes.element_id and
                    likes.element_type = 'Message'
SET post_message_users.status='deleted',
    post_message_users.deleted_time=NOW(),
    post_messages.status = 'deleted' ,
    post_messages.delete_time = now(),
    comments.status ='deleted', 
    likes.status='deleted',
    likes.delete_time=now()
WHERE post_message_users.messageid = 33 and post_message_users.received_by= 3;