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.