Embedded SQL to Update records of the Database

 


A situation may arise where we need to update many records in a database table when certain information changes or needs to be modified.
You can use embedded sql to update values in a table for all records or for those records that match a specified criteria.
For Example, one of your product suppliers has decided to increase the price of their products by 3 percent (%). Lets see the steps.

1. Open the 
application.
2. Right click and select "Developer".

3. Select “Scripts” and then select “Create”
4. In the window you must write your code.

proc upd_price()

       
start_sql “PRODUCTS_LIST “KOSMOS”
             SELECT 
             PRODUCTS.PRODUCT, PRODUCTS.PRICE
             FROM
             PRODUCTS  
             WHERE
             PRODUCTS.SUPPLIER = 2300  
       
end_sql
       q = QueryByName(“PRODUCTS_LIST”)  
       
       
rcount = TRecordCount(q)
 

       
call TFirst(q)
      
 for i = 1 to rcount

            new_price = PRODUCTS_LIST.PRICE * 1,03
            start_sql “* “KOSMOS”              
                UPDATE PRODUCTS
                WHERE (PRODUCTS = :P)          
           
end_sql
            t = QueryByName()
            call TSetParam(t, "P", PRODUCTS_LIST.PRODUCT)

            
call TSetFld(t, ”PRICE”, "Float", new_price)
            
call TExecute(t)
            
call FreeEmbSQL(t)

           
call TNext(q)
       
next

       
call FreeEmbSQL(q)
 
end


5. Save changes and close the window.
6. Call the procedure. 
    (You can call the procedure from the menu or with a button. 
     The command you use is  "call procedure name()".
     For example "call
upd_price()"   )