Home> Database> Oracle> body text

What is the usage of update in oracle

WBOY
Release: 2022-03-01 17:10:53
Original
29835 people have browsed it

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.

What is the usage of update in oracle

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]
Copy after login

Standard update

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

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

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
Copy after login

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);
Copy after login

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;
Copy after login

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;
Copy after login

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!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!