Thanks to all who replied. No speed issues and I understand about the index / expression relationship. I was just wanting to confirm a best practice concerning data integrity.
-Bob ----- Original Message ----- From: "Bill Downall" <[email protected]> To: "RBASE-L Mailing List" <[email protected]> Sent: Thursday, March 12, 2015 8:52:11 AM Subject: [RBASE-L] - Re: Update / Where best practice 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
