What is the usage of update in oracle

Usage of update in oracle: 1. Used to modify the data in the table, the syntax is "UPDATE table name SET column name = new value WHERE column name = certain value"; 2. Used to combine two tables Create a view through association and update it.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What is the usage of update in Oracle

In addition to providing the standard update statement, Oracle also provides other data update methods to deal with different application scenarios:

update_statement ::=
UPDATE {table_reference | [THE] (subquery1)} [alias]
 SET {
        column_name = {sql_expression | (subquery2)}
      | (column_name [,column_name]...) = (subquery3)} 
        [,{column_name = {sql_expression | (subquery2)}
      | (column_name [,column_name]...) = (subquery3)
     }]...
 [WHERE {search_condition | CURRENT_OF cursor_name}] [returning_clause]

Standard update

Update statement is used to modify the data in the table. The syntax in W3school:

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值。

update join view

update join view is to first create a view by associating two tables and then update the view to achieve the purpose of updating the source table:

update (select bonus
          from employee_bonus b
         inner join employees e on b.employee_id = e.employee_id
         where e.bonus_eligible = 'N') t
   set t.bonus = 0

As mentioned in the above principle, what is in the brackets is a view, and what is in the set is the field that needs to be updated. This method is direct and efficient, but it is quite restrictive. The primary key of the employees table must appear in where condition, otherwise an error will be reported, ORA-01779: Unable to modify the column corresponding to the non-key value saving table.

merge into

merge into is a unique statement of Oracle:

MERGE INTO table_name alias1
USING (table | view | sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
  UPDATE table_name SET col1 = col_val1,col2 = col2_val
WHEN NOT MATCHED THEN
  INSERT (column_list) VALUES (column_values);

Its principle is to select the data in alias2, and each item is ON (joined with alias1 condition), if they match, the update operation (Update) is performed, if they do not match, the insert operation (Insert) is performed.

@H_301_73@Cursor method

The cursor has a displayed cursor and fast cursors.

Fast Cursor

begin
for cur in (table|subquery) loop
    update_statement
end loop; 
end;

Display Cursor

SET SERVEROUTPUT ON  
DECLARE  
  CURSOR emp_cursor IS   
  SELECT empno,ename FROM emp;  
BEGIN  
FOR Emp_record IN emp_cursor LOOP     
     update_statement;  
END LOOP;  
END;

There are many benefits of using a cursor. The for loop provides us with a method to update batch data, plus Oracle's rowid physical field ( Oracle has a rowid field for each table by default, and it is a unique index), which can quickly locate the record to be updated, and can also support complex query statements.

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of What is the usage of update in oracle. For more information, please follow other related articles on the PHP Chinese website!