dhinok Posted February 15, 2006 Share Posted February 15, 2006 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 Link to comment Share on other sites More sharing options...
aspnetguy Posted February 15, 2006 Share Posted February 15, 2006 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 Link to comment Share on other sites More sharing options...
dhinok Posted February 15, 2006 Author Share Posted February 15, 2006 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 Link to comment Share on other sites More sharing options...
aspnetguy Posted February 15, 2006 Share Posted February 15, 2006 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. Link to comment Share on other sites More sharing options...
dhinok Posted February 15, 2006 Author Share Posted February 15, 2006 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? Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now