wats wrong wif this query?

[morrisoner]

I created a table called movies with fields such as genre, rating as text field.
I then decided that it would be better practice to create a table for each. So to save time I developed a query to change the text genre into an id.

Below is my movie table.

vidName text
release year(4)
description text
fk_hirePeriod tinyint(4)
fk_genre text
fk_rating tinytext(4)
fk_type text
available tinyint(4)
picture text
movieID tinyint(4)

Below is my genre table

id tinyint(4)
genre tinytext

I ran this query on the database

UPDATE movies SET movies.fk_genre = genre.id WHERE movies.fk_genre = genre.genre;

It comes up with the error "MySQL said: Unknown table 'genre' in where clause"

Any help would be appreciated, thanks!!!!

[nike]

Hello, morrisoner!

You can do it in the next sequense:
1. You leave your 'movie' table as it is.
2. You run next query:
insert into genre (genre) VALUES (select distinct fk_genre from movie)
3. and then oly small php program will save you.
a) for each row in 'genre' you execute this query:
update movie set fk_genre=$genre_id where fk_genre=$genre_genre
b) you change type of fk_genre column to int(4)

That's all!

---

Bereza Nikita
Rapid Internet Development Department
E-mail: [e-mail]
Alar Information Technologies,
URL: http://www.alarit.com

[tomhath]

As nike said, you can't assign the movies.fk_genre = genre.id because the id is an integer and fk_genre is text.

1) back up your database.

2) Alter the movies table to add a new integer column movies.fk_genreID

3)

UPDATE movies
SET movies.fk_genreID = genre.id
FROM genre
WHERE movies.fk_genre = genre.genre

4) Drop the movies.fk_genre text column

5) Back up the database again (but don't overwrite the first backup just in case...).