Help with SQL query

thegillion

I need some help with an SQL query I want to run. I have 2 tables one has reviews and one has bathrooms.
I need the average from all rating rows that reviews.bathroom_ID = bathrooms.ID then to update bathrooms.ratings

What I have so far is...

UPDATE bathrooms
   SET bathrooms.Rating = (SELECT AVG(Reviews.Rating)
                   FROM Reviews AS Rating
                  WHERE Reviews.bathroom_ID = bathrooms.id)

But it doesn't work

Error:
#1054 - Unknown column 'Reviews.Rating' in 'field list'

Reviews table EX:

ID___Bathroom_ID_____Review____Rating_____Username
1_____100___________Smells bad____0__________User
2_____100___________Its okay_______1__________User
2_____100___________Its okay_______1__________User

Bathroom table EX:

ID__________Name________________Rating____________
100________Wal-mart______________0___________________

scottalanmiller

UPDATE bathrooms
   SET bathrooms.Rating = (SELECT AVG(Reviews.Rating)
                   FROM Reviews
                  WHERE Reviews.bathroom_ID = bathrooms.id)

scottalanmiller

You have caps in your column names? You get a hand slap for that one.

scottalanmiller

@thegillion said in Help with SQL query:

SET bathrooms.Rating = (SELECT AVG(Reviews.Rating)

Pretty sure the issue is here...

thegillion

@scottalanmiller said in Help with SQL query:

You have caps in your column names? You get a hand slap for that one.

I know how to fix that I don't think that's the problem with the query

scottalanmiller

I don't think that you can use AVG in that way. It doesn't handle the column in that way.

thegillion

@scottalanmiller I know you can user AVG like this

SELECT AVG(`Rating`) FROM `Reviews` WHERE `bathroom_ID` = 100

scottalanmiller

This is MySQL, right?

thegillion

@scottalanmiller yes

scottalanmiller

@thegillion said in Help with SQL query:

@scottalanmiller I know you can user AVG like this
SELECT AVG(`Rating`) FROM `Reviews` WHERE `bathroom_ID` = 100

Yes, that I know works, too.

scottalanmiller

Is this an intentional fast lookup cache table? Because this violated the database integrity. Just a thought while I'm looking for an answer.

scottalanmiller

@thegillion said in Help with SQL query:

Maybe I'm obtuse, but why is there an AS Rating? Just remove that bit.

scottalanmiller

UPDATE bathrooms
   SET bathrooms.Rating = (SELECT AVG(Reviews.Rating)
                   FROM Reviews
                  WHERE Reviews.bathroom_ID = bathrooms.id)

scottalanmiller

Did that work?

StrongBad

@scottalanmiller said in Help with SQL query:

@thegillion said in Help with SQL query:

Maybe I'm obtuse, but why is there an AS Rating? Just remove that bit.

I was just looking at that and couldn't see where it was being used either.

thegillion

@scottalanmiller said in Help with SQL query:

UPDATE bathrooms
   SET bathrooms.Rating = (SELECT AVG(Reviews.Rating)
                   FROM Reviews
                  WHERE Reviews.bathroom_ID = bathrooms.id)

@scottalanmiller said in Help with SQL query:

Did that work?

Yes it did work thank you!!!

thegillion

@scottalanmiller said in Help with SQL query:

Is this an intentional fast lookup cache table? Because this violated the database integrity. Just a thought while I'm looking for an answer.

The bathroom table is the main table for lookups so yes and the server runs on SSDs with 4gb of ram and two 3.2ghz vCores

scottalanmiller

@thegillion said in Help with SQL query:

@scottalanmiller said in Help with SQL query:
UPDATE bathrooms
   SET bathrooms.Rating = (SELECT AVG(Reviews.Rating)
                   FROM Reviews
                  WHERE Reviews.bathroom_ID = bathrooms.id)
@scottalanmiller said in Help with SQL query:

Did that work?

Yes it did work thank you!!!

Boom, sweet! You can use the ellipses on the individual post to make it as the correct answer, too

scottalanmiller

@thegillion said in Help with SQL query:

@scottalanmiller said in Help with SQL query:

Is this an intentional fast lookup cache table? Because this violated the database integrity. Just a thought while I'm looking for an answer.

The bathroom table is the main table for lookups so yes and the server runs on SSDs with 4gb of ram and two 3.2ghz vCores

Are you going to do a scheduled batch run on this (tee hee, batch run) like at night to update the reviews?

thegillion

@scottalanmiller said in Help with SQL query:

Are you going to do a scheduled batch run on this (tee hee, batch run) like at night to update the reviews?

I was going to do that or pass the ID of the bathroom then fire it on a review or bathroom submission.

scottalanmiller

@thegillion said in Help with SQL query:

@scottalanmiller said in Help with SQL query:

Are you going to do a scheduled batch run on this (tee hee, batch run) like at night to update the reviews?

I was going to do that or pass the ID of the bathroom then fire it on a review or bathroom submission.

You could use a database trigger for that, too.