Can someone give me an extreme help

[dhinok]

Hi everyone!I have this probem. I have two table in mysql database:Table1: catID CATEGORY1 football2 swimming3 baseball4 basketball 5 skiingTable2: studentsID NAME category1 jon skiing2 may skiing3 jack skiing4 li football5 sonny basketball 6 joe baseball7 jan skiing8 kate swimmingNow my problem is:How can I update table1: cat.category by also affecting corresponding values of table2: students.category using inner join or any other sql query?Please help, thank you Jim

[aspnetguy]

How do you want to 'affect' table 2? Do you want to make sure that when cat.Category is updated that it must exist in table?Please be more specific

[dhinok]

What I meant is that:When I update field skiing (lets say to volleyball) from table CAT then it updates all the the rows from table STUDENTS that have skiing in their category field to volleyball (id 1, 2, and 7 from STUDENTS updates to volleyball)Was that specific enough? Thanks

[aspnetguy]

UPDATE cat c, students sSET c.Category = 'Volleyball',s.Category = 'Volleyball'WHERE c.Category = 'skiing' AND s.Category = 'skiing'

this query should update both tables at once.

[dhinok]

Man the query worked perfectly, thanks a lot. There's a little problem though. I added another category in table CAT with value 'polo'. But this category doesn't exist in table STUDENTS then if i try to update CAT.category polo to something else then it won't update. so what do i use for that?