Alter Mysql Multiple Table?

[iceman_rph]

Hi all,

i have a dvd/film database

I have 3 tables (table_actor, table_director , table_main)

1)table_actor has id(num), hero, no of films with hero as the columns

2)table_director has id(num), director, no of films by director

3)table_main has id(num), id_hero, id_director, heroine, film, year
(id_hero & id_director are the numeric id's from table_actor &table_director.)

-I want the switch the id_director in table_main to the directors name.
-I want to change table_director so that it is based on the heroine not the director.
i.e. id(num), heroine, no of films with heroine

What is the easiest way to do this without dumping and hardcoding? Will the ALTER statement work in anyway?

Thanks

iceman

[bastien]

to change the director from ID to name use the UPDATE statement, ALTER is for altering the table structure (indexes, keys, add/subtracting columns etc)

You'll problably need an innerjoin on dir_ID to the sub table

For the (heroine) table, I would simply copy the hero table structure as a NEW table then move the data from the main table into it