• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

PHP MySQL update supquery

I'm sure this is a pretty simple question, but I can't think of a solution at the moment.  I'm trying to update a decimal value, based on the current value of the field.

I want to reduce the amount in the field by the amount in a variable... something like this...

"currentrequest" is the current field...  So, basically I want to update current request to be it's current value minus 100.00.  

I've tried using a select subquery in there, and it tells me that I can't select the same table...  
The error I receive is: "You can't specify target table 'budget' for update in FROM clause".

Thanks for any shoves in the right direction!


$reqAmt=100.00;

$removeFromBudget=@mysql_query("
   UPDATE budget 
   SET currentrequest='currentrequest-".$reqAmt."' 
   WHERE acct='".$acctNum."' LIMIT 1");

Open in new window

0
cbastian-hill
Asked:
cbastian-hill
  • 4
  • 3
1 Solution
 
cbastian-hillAuthor Commented:
Just found the answer...   Posting in case anyone else needs it...


$removeFromBudget=@mysql_query("
   UPDATE budget 
   SET currentrequest=((SELECT currentrequest)-".$reqAmt.")
   WHERE acct='".$acctNum."' LIMIT 1");

Open in new window

0
 
nanharbisonCommented:
Can you give us the whole query? I don't see a subquery here.
0
 
cbastian-hillAuthor Commented:
That is the whole query...  The sub query is in the SET currentrequest..... line...

Are you not seeing that line?  Or am I describing it incorrectly?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nanharbisonCommented:
That's not a subquery, but glad you found the answer!
0
 
cbastian-hillAuthor Commented:
Interesting...  I was always taught that anything like that inside another query would be considered a subquery.  Would you know what it should be called, then?
0
 
nanharbisonCommented:
It just doesn't look like a subquery because UPDATE a table, set something equal to a value is a regular update query, right? But you do have to select a value first before you can change it by doing math on that value...hmm, not sure.Maybe you are right?
0
 
cbastian-hillAuthor Commented:
Hmmm....  That is what I was thinking...  The SELECT inside the UPDATE would be a subquery...  I'll have to go back to the books later, when I have some time, and see if I can find an exact definition of what makes a subquery...   Thanks for the info, either way!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now