CODEWITHSUNDEEP
mysql
Dulon Bora
Dulon Bora

Reputation: 5

Update same tables from from select value from same table mysql

i am trying to update a table column which same column from same table select. Here is the code (updated)

     public function UpdateStockIn($id, $subUnitValue) {
    $query = "UPDATE BRAND_LIST SET CURRENT_STOCK_BOTTLE = (SELECT CURRENT_STOCK_BOTTLE FROM BRAND_LIST WHERE ID = ?) + '.$subUnitValue.' WHERE ID = ? ";
    $success = 0;
    try {
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(1, $id);
        $stmt->bindParam(2, $id);

        $stmt->execute();
        $success = 1;
    } catch (PDOException $ex) {
        echo $ex->getMessage();
    }
    return $success;
}

it Show error like this You can't specify target table 'BRAND_LIST' for update in FROM clause

Upvotes: 0

Views: 117

Answers (2)

Priyesh
Priyesh

Reputation: 541

Try run these 2 sqls, The first one will store a value into mysql local variable then use into 2nd sql. 

 SELECT @old_subUnitValue := GROUP_CONCAT(table1.CURRENT_STOCK_BOTTLE) FROM BRAND_LIST AS table1 WHERE table1.ID=2;

 UPDATE BRAND_LIST AS table2 SET table2.CURRENT_STOCK_BOTTLE =  @old_subUnitValue + '.$subUnitValue.' WHERE table2.ID=2;

Upvotes: 3

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

Use the below query

$query = "UPDATE BRAND_LIST SET CURRENT_STOCK_BOTTLE = CURRENT_STOCK_BOTTLE + ".$subUnitValue."  WHERE ID = ?";

Upvotes: 0

Related Questions