UPDATE

In Oracle, the UPDATE statement is used to modify existing records in a table. This is commonly referred to as "updating" data within the database. The UPDATE statement allows you to modify one or more columns of a record based on certain conditions (specified in the WHERE clause). Here's a detailed explanation, including its usage for modifying data and notes associated with database objects.

1. General Syntax for UPDATE Statement

The basic syntax for updating records in a table is:

UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

·        table_name: The name of the table in which you want to modify data.

·        column1, column2, ...: The columns whose values you want to change.

·        value1, value2, ...: The new values to be assigned to those columns.

·        condition: A condition (usually specified using WHERE) that determines which records should be updated. If the WHERE clause is omitted, all rows in the table will be updated.

Example:

If you want to update the salary of employees in the employees table where their employee_id is 1001:

UPDATE employees

SET salary = 55000

WHERE employee_id = 1001;

This updates the salary column for the employee with employee_id = 1001.

2. Syntax to Update Comments (Notes) in Oracle

In Oracle, comments (notes) can be added to various schema objects such as tables, columns, views, indexes, and sequences using the COMMENT ON statement. While there is no UPDATE COMMENT statement in Oracle, you can modify an existing comment (which is often referred to as "updating a comment") by first dropping the existing comment and then adding a new one.

Here’s how you can update comments:

Syntax to Update a Comment:

1.     Remove the old comment (drop it):

2.  COMMENT ON <object_type> <object_name> IS NULL;

3.     Add the new comment (update it):

4.  COMMENT ON <object_type> <object_name> IS 'New comment text';

·        <object_type>: The type of object (e.g., TABLE, COLUMN, VIEW, INDEX, SEQUENCE).

·        <object_name>: The name of the object (e.g., employees, salary, employee_view, etc.).

·        IS NULL: Removes the existing comment.

·        IS 'New comment text': Adds the new comment.

Example:

To update the comment on a table employees, you would:

1.     Remove the old comment:

2.  COMMENT ON TABLE employees IS NULL;

3.     Add the new comment:

4.  COMMENT ON TABLE employees IS 'Updated table storing employee records, including their positions and salaries.';

---

3. Example of Updating Comments for Various Objects

Updating a Comment for a Table

To change the comment for the employees table:

1.     Drop the old comment:

2.  COMMENT ON TABLE employees IS NULL;

3.     Add the new comment:

4.  COMMENT ON TABLE employees IS 'Table containing employee information including departments and job roles.';

Updating a Comment for a Column

To change the comment for the salary column in the employees table:

1.     Drop the old comment:

2.  COMMENT ON COLUMN employees.salary IS NULL;

3.     Add the new comment:

4.  COMMENT ON COLUMN employees.salary IS 'Annual salary of employees, adjusted every year.';

Updating a Comment for a View

To change the comment for a view employee_view:

1.     Drop the old comment:

2.  COMMENT ON VIEW employee_view IS NULL;

3.     Add the new comment:

4.  COMMENT ON VIEW employee_view IS 'View that aggregates employee data from multiple departments.';

Updating a Comment for an Index

To change the comment for an index idx_employee_name:

1.     Drop the old comment:

2.  COMMENT ON INDEX idx_employee_name IS NULL;

3.     Add the new comment:

4.  COMMENT ON INDEX idx_employee_name IS 'Index created on employee name for faster query performance.';

---

4. Viewing Existing Comments Before Updating

Before you update (or drop) a comment, you can view the existing comments by querying Oracle’s data dictionary views.

View Comments on Tables:

SELECT table_name, comments

FROM user_tab_comments

WHERE table_name = 'YOUR_TABLE_NAME';

View Comments on Columns:

SELECT table_name, column_name, comments

FROM user_col_comments

WHERE table_name = 'YOUR_TABLE_NAME';

View Comments on Views:

SELECT view_name, comments

FROM user_view_comments

WHERE view_name = 'YOUR_VIEW_NAME';

View Comments on Indexes:

SELECT index_name, comments

FROM user_index_comments

WHERE index_name = 'YOUR_INDEX_NAME';

---

5. Things to Keep in Mind When Updating Comments

·        No Direct UPDATE for Comments: Oracle does not provide a direct UPDATE COMMENT command. You must first drop the old comment using COMMENT ON <object_name> IS NULL and then add the new comment.

·        Persistence: Once a comment is added to an object, it remains in the system until it is explicitly removed or modified.

·        Tracking Changes: Oracle does not track changes to comments automatically. If you need to track changes, you may need an external auditing mechanism or manually record the changes.

·        Character Limit: Oracle allows comments to be up to 4000 characters in length, which is typically more than sufficient for most descriptions.

·        Effect on Functionality: Updating a comment does not affect the functionality or data of the object itself. It only changes the documentation associated with that object.

---

6. Use Case for Updating Comments

Updating comments can be especially useful in the following scenarios:

·        When documentation changes: For example, when the business logic or usage of a table or column changes, you may want to update the comment to reflect the new purpose.

·        When objects evolve: As your schema evolves, you may want to adjust descriptions to reflect new functionality, add more detail, or clarify ambiguous comments.

·        Ensuring accuracy: Sometimes the original comments may become outdated or incorrect. Regularly reviewing and updating comments ensures that they remain relevant and accurate.

 

Summary of the UPDATE Process for Comments in Oracle

1.     Dropping the Old Comment: You can drop an existing comment by using the COMMENT ON statement with IS NULL.

2.     Adding the New Comment: You can update a comment by adding a new one using the same COMMENT ON statement with the desired text.

3.     No Direct UPDATE Command for Comments: Oracle does not support a direct UPDATE COMMENT command. Instead, comments are "updated" by dropping the old and adding a new comment.