CODEWITHSUNDEEP
mysqltriggers
jaredpianist
jaredpianist

Reputation: 85

update another table when updated mysql trigger

I have 2 tables, students and Math. I want to UPDATE existing data on Math.Last_name when I update the students.Last_Name using the ID I am updating in students.

Students table

CREATE TABLE `STUDENTS` (
`Date_Modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`LRN` BIGINT(12) NOT NULL AUTO_INCREMENT,
`Last_Name` VARCHAR(50) NOT NULL,
`First_Name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`LRN`)
)COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=123456789112;

MATH TABLE

CREATE TABLE `Math` (
`Date_Modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`LRN` BIGINT(12) NOT NULL,
`Last_Name` VARCHAR(50) NOT NULL,
`First_Name` VARCHAR(50) NOT NULL,
`Level` VARCHAR(3) NOT NULL,
`UT1` VARCHAR(3) NOT NULL,
`Q1` VARCHAR(50) NULL DEFAULT NULL,
`UT2` VARCHAR(50) NULL DEFAULT NULL,
`Q2` VARCHAR(50) NULL DEFAULT NULL,
`UT3` VARCHAR(50) NULL DEFAULT NULL,
`Q3` VARCHAR(50) NULL DEFAULT NULL,
`UT4` VARCHAR(50) NULL DEFAULT NULL,
`Q4` VARCHAR(50) NULL DEFAULT NULL,
`FINAL GRADE` VARCHAR(50) NULL DEFAULT NULL,
PRIMARY KEY (`LRN`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;

MY TRIGGER

CREATE TRIGGER `STUDENTS_after_update` AFTER UPDATE ON `STUDENTS` FOR EACH ROW BEGIN
UPDATE Math
SET Last_Name = NEW.Last_Name
WHERE LRN IN (SELECT LRN FROM Math where LRN = NEW.LRN);
END

Upvotes: 2

Views: 3971

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You do not need a sub select in the update command, since for each row it will pick up the new LRN value and will update all of them in the MATH table where the new.LRN matches in the MATH table. Here how the trigger would look like

delimiter //
create trigger `STUDENTS_after_update` after update on `STUDENTS`
for each row
begin
 update Math
 set Last_Name = NEW.Last_Name
 where LRN = NEW.LRN ;
end;//

delimiter ;

Note that I have added delimiter which is needed when you run them in the Mysql CLI, some other user interface like PHPMyadmin you need to select the delimiter from the user interface.

Upvotes: 1

Related Questions