1

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.

Trigger=>

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 ;

JOIN=>

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

Kindly assist.

  • Your trigger is NOT increment book field, it set it to 1 always... You need SET book := book + 1, I think. And, as I understand Your task, the trigger IS to solve it. PS. Your trigger contain 1 statement, so BEGIN..END do not require... – Akina Jun 5 '18 at 12:42
1

You can do an UPDATE of multiple tables in a single UPDATE statement, if that is really what you want to do here. It would be something like this for a specific 'rest' row (PNR = 5):

UPDATE res
INNER JOIN rest ON res.id = rest.PNR and res.TRAIN_NUMBER=rest.Tr_Num 
SET 
  res.book = res.book + 1,
  rest.age = 21 
WHERE (rest.class ='AC I' OR rest.class ='AC II' OR rest.class ='AC III') AND rest.PNR = 5;

However, and maybe I misunderstand, but I suspect what you really want to do is actually an INSERT + an UPDATE in a single transaction. You want to INSERT a new row into 'rest', and then update the relevant numbers in 'res'.

To do this, turn off autocommit in PHP, then INSERT INTO rest ... and UPDATE res ..., and then execute a COMMIT. Make sure to catch any DB-related exceptions, and handle them by executing ROLLBACK instead of COMMIT.

This way, the operation is atomic. Other DB sessions looking at the tables will always see the results of either none or all of the updates in the transaction (assuming you're not using the non-default READ UNCOMMITTED transaction isolation level).

There is not really a good way to do an INSERT and an UPDATE in a single query, except you could wrap the two statements in a stored procedure, and then call that stored procedure to execute both statements in one go. Refer to the documentation for the CREATE PROCEDURE syntax for details and/or see e.g. a stored procedure tutorial at w3resource.com. Note that it's still a good idea to wrap the two statements in a transaction inside the stored procedure.

  • @dbdemon.You're right in your opinion. I am looking for INSERT+ UPDATE in one single query – Koushik.S Jun 5 '18 at 10:55
  • Hi @Koushik.S - I've updated my answer to address that aspect of it. – dbdemon Jun 5 '18 at 12:03
  • Hi @dbdemon. The query works fine without any syntax errors. Unfortunately, it's not producing the actual result. None of my fields in the other table is getting updated. I have followed every bit of your advice. I have included the turn-off auto-commit in PHP, and post inserting the data have committed it. – Koushik.S Jun 5 '18 at 12:26
  • @Koushik.S I think that is because the data doesn't match. You need to have res row with id and TRAIN_NUMBER values that match PNR and TR_num in a row in rest that also fulfils the conditions of the WHERE clause: rest.class should be one of 'AC I', ='AC II' or ='AC III' and PNR should be 5. (You can obviously change this to fit with what you actually want.) – dbdemon Jun 5 '18 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.