Page 1 of 1

Update left join

Posted: Sat Aug 23, 2008 9:31 am
by Mini
I have 2 tables, 1 with comments and 1 with users.
What I want is when I remove an article, it should remove the comments and profile.comments-1 for each comment.

here's what I have so far:

Code: Select all

 
public function removeArticleComments($articleid)
{
    $sql = "UPDATE comments AS c
    LEFT JOIN  profiles AS p ON c.author = p.id  
    SET p.comments=p.comments-1 WHERE c.articleid=%d";
    
    $vars = array($articleid);
    $result = dbConnect::query($sql,$vars);
    echo mysql_info();
 
    $sql = "DELETE FROM comments WHERE articleid=%d";
    $vars = array($articleid);
    $result = dbConnect::query($sql,$vars);
}
 
But when a user has more then 1 comment on the article it still does -1 and not for example -4 (the rows that are affected is the same as the amount of different users that have posted).

I hope that you understand it and can give me a way of doing it :? .

Thanks 8)

Re: Update left join

Posted: Sat Aug 23, 2008 9:33 am
by onion2k
You can't use a join in an update. You'll have to do several separate updates and deletes.