I have two tables :

table1

table2

I need substract the X in tables1 with Y in tables2 | something like X = X - Y

I've tried something like this

    "INSERT INTO messages (incoming_msg_id, outgoing_msg_id, msg) 
     VALUES ({$incoming_id}, {$outgoing_id}, '{$message}'), 

     SELECT table1.X, table2.Y, (table1.X-table2.Y)
     UPDATE table1
     INNER JOIN table2 ON table1.ID = table2.ID
     SET table1.X = (table1.X-table2.Y)"

I want the UPDATE TABLE1 executed when the INSERT INTO triggered

But i did'nt notice whats wrong with the SELECT UPDATE code ? Any idea?

edit : full code

if(isset($_SESSION['id'])){
include_once "config.php";
$outgoing_id = $_SESSION['id'];
$incoming_id = mysqli_real_escape_string($conn, $_POST['incoming_id']);
$message = mysqli_real_escape_string($conn, $_POST['message']);
if(!empty($message)){
$sql = mysqli_query($conn, "INSERT INTO messages (incoming_msg_id, outgoing_msg_id, msg)
                            VALUES ({$incoming_id}, {$outgoing_id}, '{$message}')") or die();

CodePudding user response：

Use CREATE TRIGGER to execute the UPDATE whenever inserting into `messages.

CREATE TRIGGER messages_t1_t2 AFTER INSERT on messages
FOR EACH ROW
    UPDATE table1 AS t1
    JOIN table2 AS t2 ON t1.id = t2.id
    SET t1.X = t1.X - t2.Y;

CodePudding user response：

You should split it into step by steps:

• Update X = X - Y
• Insert to messages
• Select origin values, I guess the values you need is X_Old, Y, X_New. So I changed SELECT table1.X, table2.Y, (table1.X-table2.Y) to SELECT (table1.X table2.Y), table2.Y, table1.X because the data was updated.

You can check the full script here:

UPDATE table1
INNER JOIN table2 ON table1.ID = table2.ID
SET table1.X = (table1.X-table2.Y);

INSERT INTO messages (incoming_msg_id, outgoing_msg_id, msg) 
VALUES ({$incoming_id}, {$outgoing_id}, '{$message}');

SELECT (table1.X   table2.Y), table2.Y, table1.X
FROM table1
INNER JOIN table2 ON table1.ID = table2.ID;