Updating multiple tables if other table does not have relevent data

Question

I am having 4 tables.

I want to update all table for that i am using joins.

Query is working only if all tables have values. If any one table does not have record for that than it is not updating other tables

MY query is

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

Please see what change should I make so that i work properly.

have also a look here: [UPDATE multiple tables in MySQL using LEFT JOIN](http://stackoverflow.com/q/806882/1114320) — Sliq, Jul 28 '13 at 14:04

score 1 · Accepted Answer · answered Jul 28 '13 at 14:10

Your where clause is undoing the left outer join, turning it into an inner join. To fix this, move the conditions into the on clause:

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;

Updating multiple tables if other table does not have relevent data

1 Answers1