CODEWITHSUNDEEP
sqlsqlitesql-updateaverage
John Rock
John Rock

Reputation: 133

Selecting values in SQL by avg(x)

I changed the question because i was doing another part wrong.My goal is to update year information of movies which has avgrating >= 4.But I couldn't manage to list them by their avgratings :( 

/* Delete the tables if they already exist */
drop table if exists Movie;
drop table if exists Reviewer;
drop table if exists Rating;

/* Create the schema for our tables */
create table Movie(mID int, title text, year int, director text);
create table Reviewer(rID int, name text);
create table Rating(rID int, mID int, stars int, ratingDate date);

/* Populate the tables with our data */
insert into Movie values(101, 'Gone with the Wind', 1939, 'Victor Fleming');
insert into Movie values(102, 'Star Wars', 1977, 'George Lucas');
insert into Movie values(103, 'The Sound of Music', 1965, 'Robert Wise');
insert into Movie values(104, 'E.T.', 1982, 'Steven Spielberg');
insert into Movie values(105, 'Titanic', 1997, 'James Cameron');
insert into Movie values(106, 'Snow White', 1937, null);
insert into Movie values(107, 'Avatar', 2009, 'James Cameron');
insert into Movie values(108, 'Raiders of the Lost Ark', 1981, 'Steven Spielberg');

insert into Reviewer values(201, 'Sarah Martinez');
insert into Reviewer values(202, 'Daniel Lewis');
insert into Reviewer values(203, 'Brittany Harris');
insert into Reviewer values(204, 'Mike Anderson');
insert into Reviewer values(205, 'Chris Jackson');
insert into Reviewer values(206, 'Elizabeth Thomas');
insert into Reviewer values(207, 'James Cameron');
insert into Reviewer values(208, 'Ashley White');

insert into Rating values(201, 101, 2, '2011-01-22');
insert into Rating values(201, 101, 4, '2011-01-27');
insert into Rating values(202, 106, 4, null);
insert into Rating values(203, 103, 2, '2011-01-20');
insert into Rating values(203, 108, 4, '2011-01-12');
insert into Rating values(203, 108, 2, '2011-01-30');
insert into Rating values(204, 101, 3, '2011-01-09');
insert into Rating values(205, 103, 3, '2011-01-27');
insert into Rating values(205, 104, 2, '2011-01-22');
insert into Rating values(205, 108, 4, null);
insert into Rating values(206, 107, 3, '2011-01-15');
insert into Rating values(206, 106, 5, '2011-01-19');
insert into Rating values(207, 107, 5, '2011-01-20');
insert into Rating values(208, 104, 3, '2011-01-02');

These are the tables.

Upvotes: 0

Views: 229

Answers (4)

TildalWave
TildalWave

Reputation: 1667

The way you wrote your example in the question, you would be adding a string '25' at the end of the string value of your field. It would also only work on fields that are strings (text), and not numeric fields. Your example would change a field with e.g. a value of '1000' to '100025'. What you probably want to do is to add to the numeric value. Simply remove the single quotes in your example and you're done.

UPDATE tableName 
SET fieldName = fieldName + 25

If you need to target specific values, add to the end of the example above your criteria, say:

WHERE fieldName < 1000

and the changes will only be made to those records matching the criteria.

Hope this helps! ;)

EDIT: No longer relevant to OP's changed question... Please only change questions for clarity and DO NOT completely change it's meaning! AGRHHH!!!

Upvotes: 1

CL.
CL.

Reputation: 180210

For one specific movie ID, you could get the average rating with the following query:

SELECT avg(stars)
FROM Rating
WHERE mID = ?

To update all the movies with specific average ratings, use the above as a subquery:

UPDATE Movie
SET year = year + 25
WHERE (SELECT avg(stars)
       FROM Rating
       WHERE Rating.mID = Movie.mID) >= 4

Upvotes: 1

gh9
gh9

Reputation: 10703

Update TargetTable
set targetColumn = somevalue
where PrimaryKey = somevalue

If you dont have a primaryKey, you will need to find some way to uniquely identify the row or you risk updating the entire table!

Msdn page covering much of what an Update statement can and cannot do.

EDIT: 

Update TargetTable
set targetColumn = TargetColumn + somevalue

this will add 25 years to all rows

Upvotes: 0

Rafael Perez
Rafael Perez

Reputation: 162

Your code will update the year in all rows.

But like you said you want specific values, you must specify them in clause WHERE:

UPDATE mytable
 SET year = year + 25
 WHERE condition

The condition may be a :

WHERE mypk > 100

Upvotes: 0

Related Questions