CODEWITHSUNDEEP
mysqlsqlsql-updateleft-join
TheLettuceMaster
TheLettuceMaster

Reputation: 15734

Joining Tables to Update

Can I do this to update a row? This is the first time I am using a LEFT JOIN with an UPDATE.

UPDATE comments AS r

LEFT JOIN items AS i 
        ON i.items_id = r.item_id
LEFT JOIN master_cat AS c
        ON c.cat_id = i.items_id  
SET r.good = r.good + 1 
WHERE i.item = '{$item}' AND c.category = '{$cat}' AND r.review = '{$review}';

It doesn't throw an error, but it says 0 rows affected even though I have confirmed my variables are ok with right data. (Even if I hard code the data).

Edit: I have tried inner join as well, and does not work either.

Edit 2: This is actually what I am trying to do, I have these two queires and they work fine. I am trying to simplify code to one. Can this be done:

     // This query returns rate_id which I use as "$review_id" in the second query.

     $query = "
     SELECT r.rate_id
     FROM comments as r
     LEFT JOIN items AS i 
         ON i.items_id = r.item_id
     LEFT JOIN master_cat AS c
         ON c.cat_id = i.cat_id
     WHERE r.review = '{$review}' 
        AND i.item = '{$item}' 
        AND c.category = '{$cat}';"; 

    $query = "
    UPDATE comments 
    SET good = good + 1 
    WHERE rate_id = '{$review_id}';";

Upvotes: 1

Views: 111

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

Your problem is that in the UPDATE statement you have a mismatching join (cat_id with items_id):

LEFT JOIN master_cat AS c
        ON c.cat_id = i.items_id

while in the SELECT (that is working), you have the join correctly:

LEFT JOIN master_cat AS c
        ON c.cat_id = i.cat_id

Upvotes: 2

Michael Fredrickson
Michael Fredrickson

Reputation: 37388

Why are you wanting to do a LEFT JOIN? You're not using any of the columns from the joined tables to update the row... so I'm assuming that the purpose of the joined tables is to limit the rows to be incremented in comments to only those that have a match in the associated tables.

The reason that you're getting 0 rows affected is that you've placed additional conditions for the left joined tables in your WHERE clause, which is essentially turning each LEFT JOIN into an INNER JOIN.

Assuming that this is a simplified version of your query, and that you have other reasons for wanting to use LEFT JOIN, you would fix this by moving the conditions into your ON clauses:

UPDATE comments AS r
    LEFT JOIN items AS i 
        ON i.items_id = r.item_id
        AND i.item = '{$item}' 
    LEFT JOIN master_cat AS c
        ON c.cat_id = i.items_id  
        AND c.category = '{$cat}' 
SET r.good = r.good + 1 
WHERE r.review = '{$review}';

Upvotes: 3

Adam
Adam

Reputation: 480

If you're outer joining to a table c, and then you have a where clause on a field in table c, you effectively have an inner join, since the where clause will not match anything if the join condition misses.

Upvotes: 3

Related Questions