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.
start_sql “PRODUCTS_LIST” “KOSMOS”
PRODUCTS.SUPPLIER = 2300
q = QueryByName(“PRODUCTS_LIST”)
rcount = TRecordCount(q)
for i = 1 to rcount
new_price = PRODUCTS_LIST.PRICE * 1,03
start_sql “*” “KOSMOS”
WHERE (PRODUCTS = :P)
t = QueryByName()
call TSetParam(t, "P", PRODUCTS_LIST.PRODUCT)
call TSetFld(t, ”PRICE”, "Float", new_price)
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()" )