Power-Programmierung/CD1.mdf/sql/update.shr

Text File | 1988-02-02 | 5KB | 180 lines

         UPDATE

         SYNTAX:

         UPDATE column_name [,column_name...]
         FROM file_name

         It is assumed that the user understands the basics of the select
         statement.   The syntax for the update command is similar to the
         syntax for the select command.  The basic difference is that with
         the select, the data is displayed and with the update, you are
         allowed to change all data elements retrieved.

         EXAMPLES:

         The following examples use the cust table which was created in
         the tutorial:

         Retrieve all fields for customer code c2 so we can change the
         name and the rating.  SSQL will display the current value.  If
         you do not want it changed, press ENTER. The '*' signifies 'all
         columns'.

         update *
         from cust
         where code='c2';
         code            : c2
         code            :
         name            : Techoharps
         name            : Technoharps
         st              : OR
         st              :
         rating          : 15
         rating          : 12
         [ 1 updated ]

         To be more specific, we could just retrieve the name and rating:

         update name, rating
         from cust
         where code = 'c2';

         name            : Technoharps
         name            : Techoharps
         rating          : 12
         rating          : 20
         [ 1 updated ]


         Update all fields for all customers in Arizona

         update *
         from cust
         where st='AZ';


                                   UPDATE-1






         Update all fields for customer named Organomice

         update *
         from cust
         where name='Organomice';

         If you did not remember exactly how the customer name was
         spelled, you could use the like clause to update the customer
         name(s) that begin with 'Organo'.

         update *
         from cust
         where name like 'Organo%';


         The next example is a bit more involved.  We want to increase the
         rating of branch b4 customers who have purchased above average
         quantities (overall).

         ALL DETAILS ON THE ADVANCED USE OF THE WHERE CLAUSE ARE TO BE
         FOUND IN THE FULL DOCUMENTATION WHICH YOU CAN GET BY
         REGISTERING!!

         There are a variety of components in the where clause:

         1.  We use a subquery to select only "distinct" codes.  Without the
         distinct modifier we would be prompted for multiple instances of
         the customer if the branch b4 customer purchased above average
         quantities more than once.

         2.  (cc = code)
         Since the the rows we want to update are partially based on the
         branch code, we need to join the cust table and the s table since
         the rating is in the cust table and the branch code is in the s
         table.  We join them by referring to both cust and s in the from
         clause and in the where clause we set the common columns equal to
         each other (cc = code).

         3.  bc = 'b4' (branch code is equal to b4)

         4.  qty > (select avg(qty)
                    from s)
             This first calculates the average quantity of all the rows in
             the s table. Then it makes sure that the qty for the branch
             b4 customer is over that average.







                                   UPDATE-2






         update code, rating
         from cust where code in
          (select distinct code
           from cust, s
           where cc=code
             and bc='b4'
             and qty > (select avg(qty)
                                 from s));

         code            : c1
         code            :
         rating          : 20
         rating          : 21
         [ 1 updated ]





































                                   UPDATE-3