Bob, On your second question, any expression on the right side of the operator in a WHERE condition will guarantee that the index for that column will not be used, and that R:BASE will read sequentially through all rows looking for the ones that match that WHERE condition. However, the query may have already excluded a lot of rows if there is another condition in your WHERE clause that is indexed and is not using an expression.
On the first question, it probably doesn't matter. If you have a very large table, or poor performance, then try both ways and time them. Bill On Thu, Mar 12, 2015 at 9:47 AM, <[email protected]> wrote: > I have a question as to the > Update table Set column = > and > Where clause. > > Is it a good practice to have expressions in either? > > Example: > I want to increase a value in an integer column in a table, so : > > Update Sales_Tab set Qty_Sold = (Qty_Sold + .vNewQty) Where Sales_ID = > .vSalesID > > Or should I obtain the value of Qty_Sold for the record first, increase > the value and > then update the the table. > > Select Qty_Sold into vPrevQty Indicator iV1 from Sales_Tab where Sales_ID > = .vSalesID > Set var vUpdateQty = (.vPrevQty + .vNewQty) > Update Sales_Tab set Qty_Sold = .vUpdateQty Where Sales_ID = .vSalesID > > The first option should be faster, but is it a good practice? > > Secondly concerning WHERE clauses, is it a good practice to use expressions > in them. Example: > > Variable vSalesID is a text value of "1234" > Column Sales_ID is an integer column > > Update Sales_Tab set Qty_Sold = (Qty_Sold + .vNewQty) + > Where Sales_ID = (INT(.vSalesID)) > > OR > > should I convert the vSalesID into an integer variable first and > not have the expression in the WHERE clause? > > In the past, to keep the number of lines of code to a minimum, I have > used the expressions in the command or where clauses. Note, that > I often may update 3 or 4 columns in a table with one Update command, > using expressions similar to the above and have done so for many years. > > But I am wondering if this is a good practice? The help files show > examples > of the Update command using an expression, but only on one column. Would > multiple column updates with expressions be a good practice? > > Help also shows expressions in the WHERE clause as well. > > Thanks, > Bob > > >
