Search code examples
mysqlinner-join

UPDATE INNER JOIN SQL

I'm building a notification system where, the user is shown the number of new posts of people he follows. In this case I'm user 7

I have two tables

Community

  id_follower   id_followed
      7               3
      7               5
      7               7

Posts

  id_post    id_user_post  post     status
     1              3       hi        0
     2              5       hello     0
     3              9       how are   0

What I want is to update the post status to 1 of every user I follow and have posted something

This just update everything

    update posts as p
     inner join community as c on
           c.id_follower = 7
                       set p.status=1

In this case it should return 2 rows posts table updated (2,3)

Solution

  • You should add the condition for id_follower related to id_follower

     update posts as p
 inner join community as c on  c.id_follower = 7 
    and p._id_user_post = c.id_followed
  set p.status=1