Embedded SQL to Update records of the Database

A situation may arise where we need to update many records in a database table when specific information changes or needs to be modified.
You can use embedded SQL to update values in a table for all records or records that match specified criteria.
For Example, one of your product suppliers has decided to increase the price of their products by 3 percent (%). Let us 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()“ )