Hello,

I have a query that joins the two tables that goes like this.


Orders table
=========
Date              Zone   WGT   Amount
2014/12/04      1        250       null


Rate table (Dollar amount on different weights)
==================================
Zone     WGT100LBS    WGT200LBS   WGT300LBS     WGT400LBS   
1          $100                     $200              $ 300              $400
2          $125                     $225              $ 325              $425


On the orders table the weight amount is between 200 and 300 hundred pounds in weight (250).
In my example I need the dollar amount to be updated in the orders table from the rate table to reflect the amount of $200 since it is over 100 pounds and less than 300 pounds in weight. It would be easier if I could use percentages but that is not possible. I need to process as is presented.

To create my view I am am using this:

select * from Orders O, Rates.R where O. zone = '1' and R.Zone = '1' .  My real app is far more complex and do not have any issues with views.

Is there a way I can update the amount field in the Orders table. I currently copy those values to vars and process using vars and if statements one record at a time within Delphi, then repeat.  I am not sure if  I can batch update and loop with a single SQL IF or CASE statements taking a value from different columns base on weight from another table with DBISAM, which is the question.

My query is a canned query and do realize that I may have to create a separate update query for this to place the results. Looking for the best options since the query has to work over a network. I would like to do in one process for all records that need to be updated. Any help or an example is appreciated.

John Postnikoff

John

I do not know if I am following you but:

update orders set amount=if(rates.wgt100lbs > 100,1,if(rates.wgt200lb<200,2))
from orders
inner join zones on (orders.zone = rate.zone)

In the if you can create a complex logic (nesting ifs) to update the amount column in the orders table.

Eduardo (HPro)

John


It looks to me as though your query is creating a cartesian join. I'm not sure if this will become a problem for large tables, it depends on where the filter conditions are applied. Something like

select * from Orders O
LEFT JOIN Rates.R ON where O. zone  = R.Zone = '1'
AND R.zone = '1'

may be more effective.

Eduardo has given you the code for the update sql


Roy Lambert

Thanks Eduardo!,

That is what I was looking for.   I just was not sure as I never have used SQL IF statement before. I was missing the correct reference to the column as you have shown in my rates table to make it work.  Using nested IFs will be perfect for what I am doing.   

Thanks Roy for being a top contributor to fine tuning here, which is exactly why I use Elevate Software products for projects.

You guys made my day. Thanks again.

John


Jose Eduardo Helminsky wrote:

John

I do not know if I am following you but:

update orders set amount=if(rates.wgt100lbs > 100,1,if(rates.wgt200lb<200,2))
from orders
inner join zones on (orders.zone = rate.zone)

In the if you can create a complex logic (nesting ifs) to update the amount column in the orders table.

Eduardo (HPro)