insert subquery

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
neridaj
Forum Commoner
Posts: 40
Joined: Fri Jan 05, 2007 9:55 pm

insert subquery

Post by 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.

Code: Select all

 
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
Post Reply