[RBASE-L] - Re: Update / Where best practice

[Karen Tellef]

In my personal experience, I've never seen an issue with using expressions in 
an UPDATE command,
even multiple in the same update.   Don't know if there's a speed difference 
when compared to doing
a calc beforehand.

OTOH I have long ago stopped using expressions in INSERT commands.  Older 
versions would
occasionally throw an "I/O error" for me when using expressions in INSERTs, and 
even though this problem
is probably gone with current versions I have gotten into the habit of doing 
expressions outside the
insert...   FWIW

Karen


 

-----Original Message-----
From: ttc.inc <ttc....@comcast.net>
To: RBASE-L Mailing List <rbase-l@rbase.com>
Sent: Thu, Mar 12, 2015 8:47 am
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