CODEWITHSUNDEEP
phpmysqlyii
Jane Panda
Jane Panda

Reputation: 1671

How can I update multiple mysql columns of a row using the result of a select query?

I have a reviews table that contains three ways to rate an item. The items themselves then have three columns to hold the average for each value respectively.

I could do this using three nested queries in an update query, but I feel like this is inefficient... Is there a way to update them all at once?

So far I've used this as my select query:

SELECT AVG(rating_1),AVG(rating_2),AVG(rating_3) FROM items_reviews WHERE item_id = 1

I just don't know how to use the result of that query to update an item row.

Upvotes: 1

Views: 134

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

You could use an join in the UPDATE:

UPDATE items a
INNER JOIN
(
    SELECT 
        item_id,
        AVG(rating_1) AS avg1, 
        AVG(rating_2) AS avg2, 
        AVG(rating_3) AS avg3
    FROM items_reviews
    WHERE item_id = 1
    GROUP BY item_id
) b ON a.item_id = b.item_id
SET 
    a.avgrating1 = b.avg1,
    a.avgrating2 = b.avg2,
    a.avgrating3 = b.avg3

Upvotes: 1

Related Questions