1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Two Queries in single update statement

Discussion in 'Web Design' started by ngomaichi, Jun 20, 2016.

  1. ngomaichi

    ngomaichi Newbie

    Joined:
    Jun 17, 2016
    Messages:
    1
    Likes Received:
    0
    Gender:
    Female
    Hi,

    I need help on following code I have to run two queries to update records on two different tables, need to help how to run $q4 query like $q3 query.


    if(mysql_query($query))
    {
    echo '<div><div class="alert alert-success"><strong>Product added sucessfully.</strong></div></div>';
    $item_added=true;

    $product_name=$_POST['product_name']; //providing particular's name to variable $article
    $product_name;

    $q2="SELECT SUM(`quantity_in`) FROM `av_products_in` WHERE `product_name`='$product_name' Group By `product_name`";
    $q2_run=mysql_query($q2);
    $q2_total=mysql_result($q2_run,0);
    $q2_total;


    $q1="SELECT SUM(`quantity`) FROM `av_orders` WHERE `product_name`='$product_name' Group By `product_name`";
    $q1_run=mysql_query($q1);
    $q1_total=mysql_result($q1_run,0);
    $q1_total;

    $total=$q2_total-$q1_total;
    $total;
    $qr="SELECT piece_per_box FROM av_products WHERE `product_name`='$product_name'";
    $piece_per_box=mysql_result(mysql_query($qr),0);
    $box=$total/$piece_per_box;
    $q3="UPDATE av_products_in SET balance=$total,boxes=$box WHERE product_name='$product_name'";

    // echo $q4="UPDATE av_products SET boxes=$box WHERE product_name='$product_name'";

    // 2 lines can be commented-
    if(mysql_query($q3))

    echo "Quantity updated";
    }
    else
    echo '<div class="alert alert-success"><strong>Problem in adding Product.</strong></div>';


    Thanks
     
  2. mizpress

    mizpress Newbie

    Joined:
    Jun 7, 2015
    Messages:
    21
    Likes Received:
    3
    Gender:
    Male
    Home Page:
    You can't update fields from multiple tables on a single update query. There error you are getting is because this is not permitted:

    Since you can only update one table per update command. However you can join them if you only changing value from one table only