I use the one line update all the time. Update Sales_Tab set Qty_Sold = (Qty_Sold + .vNewQty) Where Sales_ID = .vSalesID
Dan Goldberg From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Thursday, March 12, 2015 6:47 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Update / Where best practice 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
