如何在单个QUERY中插入一个表并在另一个表中更新UPDATE？

I'm currently creating some sort of inventory system.

我目前正在创建某种库存系统。

I have master_tbl where in I save the items. In master_tbl, I have column qty_left or the available stock left.

我有master_tbl在哪里保存项目。在master_tbl中,我有列qty_left或剩余的可用库存。

I also have the table issuance_tbl where in I save all the transaction in issuing items. In this table there is issued_qty.

我还有表issuance_tbl,其中我保存了发布项目中的所有事务。在此表中有issued_qty。

My problem is how can I INSERT a row into issuance_tbl at the same time UPDATE the master_tbl.qty_left. (master_tbl.qty_left - issuance_tbl.issued_qty).

我的问题是如何在更新master_tbl.qty_left的同时将一行插入issuance_tbl。 (master_tbl.qty_left - issuance_tbl.issued_qty)。

Is it possible?

可能吗?

4 个解决方案

#1

I think the best way is using Stored Procedure. You can have bunch of SQL statements with error handling and ACID transactions in one place. This is because if your first query executes and the second fails, you may need to rollback transactions. Stored procedures allow all this fancy but reliable stuff.

我认为最好的方法是使用存储过程。您可以在一个地方拥有一堆带有错误处理和ACID事务的SQL语句。这是因为如果您的第一个查询执行而第二个查询失败,您可能需要回滚事务。存储过程允许所有这些花哨但可靠的东西。

You can start here: http://forums.mysql.com/read.php?98,358569

你可以从这里开始:http://forums.mysql.com/read.php?98,358569

#2

I'm not completely confident that 'If there's any way to do such thing': You need to do it in steps, LIKE THIS:

我并不完全相信“如果有任何办法可以做这样的事情”:你需要分步进行,就像这样:

$result = $this->db->insert($table);//Example function to insert inventory item
$insert_id = $this->db->insert_id();//Example function to get the id of inserted item
if($result)
$res = $this->db->update($id,$data,$table);//Example function to update data of quantity table

if(!$res)
{
$this->db->delete($insert_id,$table);s
$result = '-1';
}
return $result;

Hope this might help

希望这可能有所帮助

#3

here is the example:

这是一个例子:

    <form method="post">
    QTY:<input type="text" name="qty_left"></input>
        <input type="submit" name="submit" value="update"></form>

   <?php
         ////////your config db
     $qty = $_POST['qty_left'];
     if(isset($_POST['submit']);
     $sql = mysql_query("insert into issuance_tbl (issued_qty) values (".$qty.") ");
     $sql1 = mysql_query("update master_table set qty_left= ".$qty."");
     ?>

#4

$con = mysqli_connect($host, $user, $password, $database);
...
$issued_qty = '10'; //some value

$insert_query = "insert into issuance_table (issued_qty, ...) values ('$issued_qty', ... ) ;";    
$update_query = "update master_tbl set qty_left = (qty_left - ".$issued_qty.")  where ....";    
mysqli_multi_query($con, $insert_query.$update_query);

#1