RelationalDBDesignRelationalDBDesign


Table Modification   «Prev 

Updating Rows Syntax

UPDATE <table_name> <table alias>
SET table_alias.object_type_name.attribute_name=
  <variable>
WHERE <clause>;
Updating rows syntax

Location 1 The UPDATE statement specifying the table
Location 2 Traversing down to the attribute within the object type associated with the object table
Location 3 The WHERE clause

UPDATE

An UPDATE statement modifies existing data, following the same transaction control rules as INSERT. The syntax for UPDATE is
UPDATE table_name
SET column_name = select_statement | value [, column_name = value]
[WHERE where_clause | WHERE CURRENT OF cursor];

The table_name can be any table, synonym, or updatable view. Column_name is any column in the table_name specified. The SET clause can include more than one column_name in a comma-delimited list. Columns can be set equal to an integer, a variable, or any valid expression. They can also be set equal to the result of a subselect. The optional WHERE CURRENT OF clause is useful when working with a cursor that is declared with a FOR UPDATE clause. The where_clause can be any column in the table compared to any expression. The WHERE CURRENT OF clause works with UPDATEs and DELETEs, and says to operate against the current record from the cursor.
The first example performs an update against a table, with its value derived from a variable of the same type as the column.
--Available online as part of Update.sql
SET SERVEROUTPUT ON
DECLARE
v_num_pages BOOKS.NUM_PAGES%TYPE;
v_isbn BOOKS.ISBN%TYPE := '72230665';
BEGIN
SELECT num_pages
INTO v_num_pages
FROM books
WHERE isbn = v_isbn;
DBMS_OUTPUT.PUT_LINE('Number of pages before: '||v_num_pages);
v_num_pages := v_num_pages + 200;
UPDATE books
SET num_pages = v_num_pages
WHERE isbn = v_isbn;
DBMS_OUTPUT.PUT_LINE('Number of pages after: '||v_num_pages);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
ROLLBACK;
END;
/