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

[Weedpacket]

One question that leaps instantly to mind is why you make the same selection three times. Then that gets replaced with the question about why you're selecting from the same record you're updating to when you already have the record.

Update invoice, set inv_amt_paid = inv_amt_paid + $paid, .... where inv_number = $invnum

[Roger_Ramjet]

Weed[packet is absolutely right, that is how to do it.

The reason your original query will not work is
UPDATE syntax

Currently, you cannot update a table and select from the same table in a subquery.