MySql: Update more than one column

Question

My timetable table looks like this:

  id  period   mon   mon_tch   tue   tue_tch   wed   wed_tch
  --  ------   ---   -------   ---   -------   ---   -------
   1   prd1     4       5       8        7      6       3    
   2   prd2     6       3       4        5      8       7

My teacher-subject table:

   id   tchr   subject
   --   ----   -------
    1     5       4
    2     7       8

where values in mon is the subject_id and mon_tch is the teacher_id and so on.

When admin changes the subject of a teacher in the 'teacher-subject' table via a form (example: subject of teacher with id 5 is changed from 8 to 9), I want to update my timetable table with the new subject assigned.

(consider the subject field in the teacher-subject table will be updated somehow).

I don't understand exactly what you triying to.do. maybe something like a update with join? http://stackoverflow.com/questions/15209414/mysql-update-join — BenB, Oct 03 '14 at 10:15
@batz : nop, i dont want to join any table.. just replace multiple column values in one update query. — arshad, Oct 03 '14 at 10:32
Somthing like "UPDATE table_name SET field1=new-value1, field2=new-value2 where id=1"¿ — BenB, Oct 03 '14 at 10:34
can you please give some demo code, so we understand what you exactly want — deemi-D-nadeem, Oct 03 '14 at 10:52
@deemi: when i update the teacher of a subject in the subject-teacher table using "update timetable set tchr=4 where subject=4", the mon_tchr and tue_tchr columns in the teacher-subject table should be changed.. not automatically but what query should be used to do this? — arshad, Oct 03 '14 at 11:09
and what is in mon_tch, tue_tch, are you save teacher id or subject id — deemi-D-nadeem, Oct 03 '14 at 11:19
mon contains the subject_id and mon_tch contains teacher_id @deemi — arshad, Oct 03 '14 at 11:26
@ArshadMuhammed: you make a very difficult DB structure, if you understand what "strawberry" says in his answer then you have your solution....if you want solution with "your" db structure ... its a very long but possible solution ..... what you say — deemi-D-nadeem, Oct 03 '14 at 11:34
@deemi : i understand that Strawberry's solution by normalizing my table is the smartest way, but i am curious about the update query that updates multiple columns. :) — arshad, Oct 03 '14 at 11:44
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/62407/discussion-between-deemi-and-arshad-muhammed). — deemi-D-nadeem, Oct 03 '14 at 11:46

score 1 · Accepted Answer · answered Oct 03 '14 at 10:38

A normalised design might look like this...

period day subject teacher 
--------------------------
 1     mon   4       5       
 1     tue   8       7  
 1     wed   6       3    
 2     mon   6       3       
 2     tue   4       5      
 2     wed   8       7

... where (period,day) constitutes a compound PK. That said, there may still be some redundancy here.

MySql: Update more than one column

1 Answers1