I'm trying to create a store procedure in mysql and it is giving me errors and I don't know why since the sql code works just find.

Here is the sql code:

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (
select AVG(Score) as AVG, MovieCode
from UserMovies
group by MovieCode
);

UPDATE Movies
set Score =(
select AVG
from table2
where Movies.Code=table2.MovieCode );

DROP TABLE IF EXISTS table2;

Thanks for the help.

correction to code, but still need help.

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (
select AVG(Score) as aScore, MovieCode
from UserMovies
group by MovieCode
);

UPDATE Movies
set Score =(
select aScore
from table2
where Movies.Code=table2.MovieCode );

DROP TABLE IF EXISTS table2;

Got it to work.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.