======================================================================
     POSTING WITH ONE COMMAND
     ======================================================================
     PRODUCT:  R:BASE             VERSION :  4.0
     AREA   :  UPDATING           CATEGORY:  POSTING        DOCUMENT#: 658
     ======================================================================
 
     An article in the January/February 1992 issue of the Exchange showed how 
     to use the SELECT command to write a file of UPDATE commands to do 
     posting without writing a DECLARE CURSOR program. This is no longer the 
     easiest and fastest method. Now, with R:BASE 4.0, throw out that code 
     doing SELECT commands, throw out that code doing DECLARE CURSOR routines, 
 
     and do posting using just the UPDATE command.
 
     The UPDATE command has been extended in R:BASE 4.0 to allow multiple table
 updates. It works similar to a multi-table select. You still specify the table
 and column to be updated, but now the data you put into the column can come fr
om another table or from a view.
 
     The following rules apply when using the new UPDATE syntax:
 
     <> The table being updated must appear in the FROM clause
     <> The tables in the FROM clause must have a one-to-one relationship. 
        In other words, each row in the table being updated must match only 
        one row in the other table or view that the data is coming from.
 
     Two Examples of the New Syntax
     ==============================
     1. A new table containing address changes has just been imported. You 
     need to update the addresses in the master table with the new addresses. 
     Prior to R:BASE 4.0, this task would have required programming; now you 
     can do it with one command, as follows:
 
        UPDATE master_list SET address = t2.address,city=t2.city, + 
        state=t2.state, zipcode=t2.zipcode FROM master_list t1, + 
        new_addr t2 WHERE t1.cust_id = t2.cust_id
 
     This command links the two tables, master_list and new_addr, then 
     puts the data from the columns address, city, state, and zipcode in 
     new_addr into the corresponding columns in master_list where the 
     customer identification number matches. 
 
     Notice how correlation names are used to identify and link the tables 
     in the WHERE clause just like with a multi-table select or a view. No 
     programming is required, and you get faster updates.
 
     2. In normal daily processing, an invoice total is updated with the sum 
     of the items ordered, a relationship comparable to the relationship 
     between the transdetail and transmaster tables in the CONCOMP sample 
     database. The column netamount in the transmaster table holds the sum 
     of column extprice from the transdetail table for all the matching 
     transaction records. 
 
     In this example the goal is to update one row with a calculation based 
     on many rows. A one-to-one relationship does not exist between the two 
     tables to be used in the update. To use the new UPDATE syntax you first 
     need to establish a one-to-one relationship. Create a view from the 
     many-sided table to accomplish this as shown below; be sure to give 
     your view column names.
 
        CREATE VIEW trans_sum (transid,total_sales) AS +
         SELECT transid,SUM(extprice) FROM transdetail GROUP BY transid
 
     Then use the view in the UPDATE command, as shown below. The view has 
     a one-to-one relationship with the transmaster table.
 
        UPDATE transmaster SET netamount = total_sales +
         FROM transmaster t1, trans_sum t2 WHERE t1.transid=t2.transid