mysql table synchronization

Question

So imagine 2 big mysql tables imported from Excel.

Table1 has the field SNameID, and other fields describing SNameID (Age, Size).

SNameID Age Size

Table2 has the field TeacherID, and for each teacher there is/are student/s SNameID and the describing fields (Age, Size).

TeacherID SNameID Age Size

The idea is: the Table2 has the TeacherID data and the relationship information: this are his students. It's quite a bad architecture ... but I have to use both tables as they are further.

Please tell me, when some change occurs in the Age and/or Size in any of the table, can I make mysql automatically update the other table according to the SNameID?

I just can't figure it out ... and its the problem in many of my tables ... All I need is a hint where or what to read - I can figure it our afterwards and will post a solution here as well.

In the end I did it the hard way - when I update some data, I execute an update query, and several other queries to update the same data in other tables. It works OK. — Sergiu
– Sergiu, Commented Mar 21, 2012 at 15:58

Kaii · Accepted Answer · 2012-02-24 22:30:31Z

1

You can setup a TRIGGER AFTER UPDATE to automatically sync changes in the data:

DELIMITER |
DROP TRIGGER IF EXISTS `teacher_update`|
CREATE TRIGGER `teacher_update` AFTER UPDATE ON `teacher`
FOR EACH ROW
BEGIN
  UPDATE student s SET s.Age = NEW.Age, s.Size = NEW.Size WHERE s.SNameID = NEW.SNameID;
END;
|
DROP TRIGGER IF EXISTS `student_update`|
CREATE TRIGGER `student_update` AFTER UPDATE ON `student`
FOR EACH ROW
BEGIN
  UPDATE teacher t SET t.Age = NEW.Age, t.Size = NEW.Size WHERE t.SNameID = NEW.SNameID;
END;
|
delimiter;

However, what you REALLY should do is normalize this database design.

Drop the Age and Size fields from the teacher table and when you SELECT, fetch the information from the student table using the foreign key.

SELECT t.*, s.* FROM teacher t LEFT JOIN student s ON t.SNameID = s.SNameID

edited Feb 24, 2012 at 22:30

answered Feb 24, 2012 at 16:49

Kaii

20.6k3 gold badges40 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kaii Over a year ago

@Sergiu welcome to StackOverflow! If you like the answer and i was able to help you, please click on the checkmark beside the answer to accept it.

Collectives™ on Stack Overflow

mysql table synchronization

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related