I am running into a problem updating my two tables at the same time. My first table res which needs to be updated every time the other table rest gets input from a HTML Form.
Table res=> http://sqlfiddle.com/#!9/82bfc2/2 . column id =>Primary Key This table holds all the information related to Train Journey. This has columns(ie. col AC_I=no. of tickets at the begging, book= tickets booked and bal=balance of tickets from the total tickets) which describe against which Train how many tickets are available, how many were booked and what's the current balance when the user is logged in to book their tickets.
My Second table rest http://sqlfiddle.com/#!9/557e4. column PNR =>Primary Key This table takes input from users.
My question is how to update table res and update columns which is created to store the tickets booked for each class and for a particular train name or train number.
For example: IF customer books a seat in TRAIN NAME ='A', CLASS='AB' then my other table with the same row having a column "BOOK" will auto-increment.
What I tried is My SQL Trigger and Join. Both are not working at this time.
DELIMITER $$ DROP TRIGGER IF EXISTS t1i $$ CREATE TRIGGER t1 AFTER INSERT ON rest FOR EACH ROW BEGIN UPDATE res SET book = 1 WHERE Tr_Num = NEW.Tr_Num; END $$ DELIMITER ;
UPDATE res JOIN rest ON res.id = rest.PNR and res.TRAIN_NUMBER=rest.Tr_Num SET res.book = 1 where case when rest.class ='AC I' then res.book=1 when rest.class ='AC II' then res.book=1 when rest.class ='AC III' then res.book=1 end
Result shows 0 Rows affected