Solved

PHP MySQL update supquery

Posted on 2011-02-24
7
394 Views
Last Modified: 2012-05-11
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
Comment
Question by:cbastian-hill
  • 4
  • 3
7 Comments
 

Accepted Solution

by:
cbastian-hill earned 0 total points
ID: 34972441
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
 
LVL 17

Expert Comment

by:nanharbison
ID: 34972454
Can you give us the whole query? I don't see a subquery here.
0
 

Author Comment

by:cbastian-hill
ID: 34972473
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 17

Expert Comment

by:nanharbison
ID: 34972943
That's not a subquery, but glad you found the answer!
0
 

Author Comment

by:cbastian-hill
ID: 34972973
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
 
LVL 17

Expert Comment

by:nanharbison
ID: 34973097
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
 

Author Comment

by:cbastian-hill
ID: 34973139
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

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question