How to INSERT in one table and UPDATE in another in single QUERY?

Question

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.

I also have the table issuance_tbl where in I save all the transaction in issuing items. In this table there is 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).

Is it possible?

coz, I have a single form, that accept record for issuance_tbl. also includen in that form is the 'issued_qty' that i needed to update the master_tbl.qty_left. — ChocoMartin, Aug 14 '13 at 07:08
look this answer, maybe it help you http://stackoverflow.com/a/4754333/2550094 — Lan, Aug 14 '13 at 07:14

score 0 · Answer 1 · answered Aug 14 '13 at 07:07

0

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.

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

answered Aug 14 '13 at 07:07

user2483744

273
1
5
11

I'm newbie programmer and i have no idea about Stored Procedure. :( – ChocoMartin Aug 14 '13 at 07:11
@ChocoMartin: there are millions of tutorials regarding stored procedures on the internet (the MySQL has one as well - and you did read the manual, did you?). You should really read them. – a_horse_with_no_name Aug 14 '13 at 07:16
Then I would suggest to invest sometime in it. It will really help you write enterprise level applications in the future. You will find thousands of examples and tutorials on this topic. – user2483744 Aug 14 '13 at 07:16

Dante Ditan · Answer 2 · 2013-08-14T07:19:00.233

0

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."");
     ?>

edited Aug 14 '13 at 07:19

answered Aug 14 '13 at 07:12

Dante Ditan

34
4

1

`UPDATE FROM` is deprecated. – Rápli András Aug 14 '13 at 07:14
i forgot to remove the from – Dante Ditan Aug 14 '13 at 07:19
anyway, all mysql_* are deprecated. and your update query will be based on what `where` condition ? – B F Aug 14 '13 at 09:58
it depends on the database that you need to update from where condition if its a primary key or foreign key. – Dante Ditan Aug 14 '13 at 23:21

score 0 · Answer 3 · answered Aug 14 '13 at 07:14

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

score 0 · Answer 4 · answered Aug 14 '13 at 07:26

$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);

How to INSERT in one table and UPDATE in another in single QUERY?

4 Answers4