Adding COUNT(*) to a value using a subquery

[deadpool42]

Here's the query I'm trying to figure out:

UPDATE vb_quotes AS quotes
SET quotes.rating = quotes.rating +
( SELECT COUNT( * ) FROM vb_quoteratings AS ratings
WHERE ratings.quoteid = quotes.quoteid AND ratings.rating > 2 )

I'm guessing the problem is referring to the main table from withing the subquery, but I can't think of a way to do this with JOINs.

What I'm trying to do is rebuild a table of quote ratings, converting everything that was previously rated greater than 2 (in the vb_quoteratings table) to be a rating of 1 and add them all up in the vb_quotes table. The only way I know of would be to run hundreds of queries (one for each quoteid).

 

[dalchri]

UPDATE vb_quotes AS quotes, (
SELECT quoteid, COUNT(*) AS RatingCount
FROM vb_quoteratings AS ratings
WHERE ratings.rating > 2
GROUP BY quoteid) AS ratings
SET quotes.rating = quotes.rating + ratings.RatingCount
WHERE quotes.quoteid = ratings.quoteid

 

[r937]

if this is a one-time fix, run this --

create table quote_ratings
select quoteid
     , count(*) as r
  from vb_quoteratings
 where ratings.rating > 2
group
    by quoteid

then run this --

UPDATE vb_quotes, quote_ratings
   set vb_quotes.rating 
     = vb_quotes.rating + quote_ratings.r
 where vb_quotes.quoteid = quote_ratings.quoteid

then drop the quote_ratings table

remember, as with all data modification queries that you find on the internet, take a backup first

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)