从表中选择并更新另一个表SQL

i'm trying to get 'business_id' and 'rating' from the table 'reviews' where 'review_id' is $approveReviewID

我正试图从'评论'表中获取'business_id'和'评分',其中'review_id'是$ approveReviewID

and then update 'rating' in 'business_details' with this retrieved rating from 'reviews' where 'business_id' is equal to that retrieved.

然后使用来自“评论”的检索评级更新'business_details'中的'评级',其中'business_id'等于检索到的评分。

i have these two queries,

我有这两个问题,

SELECT business_id, rating FROM reviews WHERE review_id = '$approveReviewID';

UPDATE business_details SET rating = rating + $rating WHERE business_id = '$businessID';

is there a way to join these queries into one?

有没有办法将这些查询加入一个?

2 个解决方案

#1

Yes, you can use the update-join syntax:

是的,您可以使用update-join语法:

UPDATE business_details bd
JOIN   reviews r ON bd.business_id = r.business_id
SET    bd.rating = bd.rating + r.rating
WHERE  r.review_id = '$approveReviewID'

#2

Use simple "Inner join" in from clause to join both tables

在from子句中使用简单的“内部联接”来连接两个表

UPDATE  bd
SET     rating = bd.rating + rvs.rating
FROM    reviews rvs
        INNER JOIN business_details bd ON db.business_id = rvs.business_id
WHERE   rvs.review_id = '$approveReviewID'

#1