insert subquery

[neridaj]

Hello,

 

I'm trying to do an insert that contains a few subqueries to update some invoice amounts but my query isn't working and I think there might be a better way to do this like maybe a stored procedure or function. I'm sure there is a more elegant way to do this so if you have any ideas please let me know.

 

function update_invoice($invnum, $paid)
{
  // update existing invoice
  $conn = db_connect();

  // add current amount paid to new amount paid and subtract from balance
  if(!$conn->query("UPDATE invoice SET inv_amt_paid = (SELECT inv_amt_paid FROM invoice WHERE inv_number = $invnum) + $paid, inv_amt_balance = (SELECT inv_amt_balance FROM invoice WHERE inv_number = $invnum) - (SELECT inv_amt_paid FROM invoice WHERE inv_number = $invnum) WHERE inv_number = $invnum);"))
    throw new Exception('Invoice could not be updated.');

  return true;
}

 

Thanks,

 

J

[fenway]

First, post the raw query string.

Second, define "not working".

Third, post the error message.