Modifies data in rows of a table.
exec sql [at connection_name] update table_name
set [table_name]
column_name1 = {expression1
| NULL | (select_statement)}
[, column_name2 =
{expression2 | NULL
| (select_statement)}]…
[from table_name
[, table_name]…
[where {search_conditions | current of cursor_name}]
end-exec
The name of a table or view, specified in any format that is valid for the update statement in Transact-SQL.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 STORE-NAME PIC X(40).
01 DISC-TYPE PIC X(40).
01 LOWQTY PIC S9(9) COMP.
01 HIGHQTY PIC S9(9) COMP.
01 DISCOUNT PIC S9(9) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL DECLARE upd_cursor CURSOR FOR
SELECT s.stor_name, d.discounttype, d.lowqty,
d.highqty , d.discount
FROM stores s, discounts d
WHERE s.stor_id = d.stor_id END-EXEC.
EXEC SQL OPEN upd_cursor END-EXEC.
PERFORM FETCH-LOOP UNTIL SQLCODE = 100.
EXEC SQL CLOSE upd_cursor END-EXEC.
EXEC SQL DEALLOCATE CURSOR upd_cursor END-EXEC.
EXEC SQL COMMIT WORK END-EXEC.
...
FETCH-LOOP.
EXEC SQL FETCH upd_cursor INTO :STORE-NAME, :DISC-TYPE,:LOWQTY ,
:HIGHQTY,:DISCOUNT END-EXEC.
IF SQLCODE = 100
DISPLAY "NO MORE RECORDS TO FETCH. END OF PROGRAM RUN."
ELSE
DISPLAY "NEW DISCOUNT : "
ACCEPT DISCOUNT
EXEC SQL UPDATE discounts
SET discount = :DISCOUNT
WHERE CURRENT OF upd_cursor END-EXEC
END-IF.
END-FETCH-LOOP.
This reference page mainly describes aspects of the Transact-SQL update statement that differ when the statement is used in Embedded SQL. See the Adaptive Server Enterprise Reference Manual.
Host variables can appear anywhere in an expression or in any where clause.
You can use the where clause to update selected rows in a table. Omit the where clause to update all rows in the table. Use where current of cursor_name to update the current row of an open cursor.
When where current of cursor_name is specified, the statement must be executed on the connection specified in the open cursor statement. If the at connection_name clause is used, it must match the open cursor statement.
close, delete cursor, fetch, open, prepare
