UPDATE with an if statement

[niche]

I know this isn't right, but I think you can get tthe idea of what I'm trying to do. Assuming I can put an if statement in an UPDATE query, how should I change this query so it works?mysql_query(UPDATE plan SET curref = 0, abc = if ("abc - decrement <= lap) then abc = abc - decrement WHERE curref >= ddecpt AND ddecpt > 0");If I can't put an if statement in an UPDATE query, how should I re-think my approach? Thanks

[aspnetguy]

You can't use the if statement in the natural flow of the SQL statement. Would need to create a MySQL function or stored procedure which usually is not supported my free or shared hosting.

[niche]

I've reviewed the links and googled the subjects and I'm lost. Can you provide a few more hints for starters?

[jeffman]

And this is why you can make an entire career doing nothing but SQL.

[niche]

So...I put the if statement outside and the update inside

require_once "connect_to_mysql.php";$result2 = mysql_query("SELECT * FROM plan WHERE curref >= ddecpt AND ddecpt > 0 AND curwipprice - dcrement >= lap") or die(mysql_error());while ($row = mysql_fetch_array($result2)) {  $id = $row['id'];  echo $id . '<br/>';    //echo var_dump($row) .'<br/>';  //echo '<br/>';  require_once "connect_to_mysql.php";  //mysql_query("UPDATE plan SET curref = 0, lastwipprice = curwipprice, cuwipprice = curwipprice - decrement WHERE id = " . $id);   mysql_query("UPDATE plan SET lastwipprice = curwipprice,  cuwipprice = curwipprice - decrement  WHERE id = " . $id); }

Why does mysql_query("UPDATE plan SET curref = 0, lastwipprice = curwipprice, cuwipprice = curwipprice - decrement WHERE id = " . $id); workAND mysql_query("UPDATE plan SET lastwipprice = curwipprice, cuwipprice = curwipprice - decrement WHERE id = " . $id); does not??

[niche]

The solution to my last post was a misspelled column. Please excuse me.Here's what I came-up with as my solution to this topic:

require_once "connect_to_mysql.php";$result2 = mysql_query("SELECT * FROM plan WHERE curref >= ddecpt AND ddecpt > 0 AND curwipprice - dcrement >= lap") or die(mysql_error());while ($row = mysql_fetch_array($result2)) {  $id = $row['id'];  $curwipprice = $row['curwipprice'];  $dcrement = $row['dcrement'];  //echo $id . '<br/>';    $newprice = $curwipprice - $dcrement;  //echo $newprice. '<br/>';    //echo var_dump($row) .'<br/>';  //echo '<br/>'; require_once "connect_to_mysql.php";  mysql_query("UPDATE plan SET lastwipprice = curwipprice, curwipprice = " . $newprice . " , curref = 0  WHERE id = " . $id); }

I am interested in what Deirdre's Dad meant by, "And this is why you can make an entire career doing nothing but SQL"? If it's just venting that's OK! He does great work and we value his help.

[aspnetguy]

He was responding to your comment about being lost reading those links about functions and stored procedures. The are very specialized careers doing nothing but managing databases and writing functions and stored procedures for programmers to use. It isn't as confusing as it might seem at first. If you read a few tutorials on the subject you will get it. Most hosting companies do not let you use functions and stored procedures unless you have a dedicated server anyway.For the first 3 years of my current job all database access was through stored procedures (using MS SQL Server). It was time consuming and painful. Once I convinced my boss to let me use NHibernate it cut time writing data access code by at least 75%.

[niche]

Can you refer me to some tutorials on functions and stored procedures?

[jeffman]

Imagine the people who do the inventory for a trucking company or Amazon.com. It's nothing but database all day, everyday. You need a special personality for that kind of work.

[ShadowMage]

Imagine the people who do the inventory for a trucking company or Amazon.com. It's nothing but database all day, everyday. You need a special personality for that kind of work.

True but they most likely have some kind of third party software to automate many of those processes. I'd bet that the inventory personnel know very little (more likely none at all) about SQL or any other kind of programming. For example, where I work, we use a software suite known as Vantage to keep track of inventory, orders, and other such things. Those who maintain inventory and manage the orders wouldn't know how to write a simple "select everything from one table" kind of query, let alone stored procedures.

[jeffman]

Yeah, those kind of places. The college where I work does a lot of in-house research, and a lot of the queries are pretty customized, so there are not set routines. Like maybe I want a report of all the students I've taught in the last ten years who come from a certain county in my state, including their High School GPA and ACT scores. That gets coded on a command line. They save stuff, too, though, so if I want it again, the girl just enters an alias or something.

[ShadowMage]

Yeah, those kind of places. The college where I work does a lot of in-house research, and a lot of the queries are pretty customized, so there are not set routines. Like maybe I want a report of all the students I've taught in the last ten years who come from a certain county in my state, including their High School GPA and ACT scores. That gets coded on a command line. They save stuff, too, though, so if I want it again, the girl just enters an alias or something.

We write a lot of custom reports and such, but the majority of the inventory management (such as adding/removing parts, moving parts to a new bin, etc.) are handled by Vantage.

[aspnetguy]

Can you refer me to some tutorials on functions and stored procedures?

this site is pretty good http://www.mysqltutorial.org/mysql-stored-...e-tutorial.aspx

[niche]

Looks real promising. Thanks for everyone's help especially aspnetguy, Deirdre's Dad, and ShadowMage. You've made this a very beneficial experience.