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.


    UPDATE res 
      SET book = 1 
      WHERE Tr_Num = NEW.Tr_Num; 
  END $$ 


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 

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, 2018 at 12:42

1 Answer 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):

INNER JOIN rest ON res.id = rest.PNR and res.TRAIN_NUMBER=rest.Tr_Num 
  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, 2018 at 10:55
  • Hi @Koushik.S - I've updated my answer to address that aspect of it.
    – dbdemon
    Jun 5, 2018 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, 2018 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, 2018 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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