Experts Exchange connects you with the people and services you need so you can get back to work.

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

cbastian-hill

Asked: 2011-02-24

Advertise Here

1 Solution

cbastian-hillAuthor Commented: 2011-02-24

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

LVL 17

nanharbisonCommented: 2011-02-24

Can you give us the whole query? I don't see a subquery here.

cbastian-hillAuthor Commented: 2011-02-24

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?

LVL 17

nanharbisonCommented: 2011-02-24

That's not a subquery, but glad you found the answer!

cbastian-hillAuthor Commented: 2011-02-24

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?

LVL 17

nanharbisonCommented: 2011-02-24

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?

cbastian-hillAuthor Commented: 2011-02-24

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!

Question has a verified solution.

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

PHP MySQL update supquery

Join & Write a Comment Already a member? Login.

Featured Post