Update left join

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Mini
Forum Newbie
Posts: 23
Joined: Mon Dec 04, 2006 4:39 am
Location: Netherlands

Update left join

Post 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)
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Update left join

Post by onion2k »

You can't use a join in an update. You'll have to do several separate updates and deletes.
Post Reply