Oracle Update Multiple Columns

Updating multiple columns in a database table is a common phenomenon for database administrators. For example, you might need to set a new value for a column based on the value of another column. For example, you may update the salary value based on the skill level. Since the skill level can change several times over time, you can find yourself making updates to such a column.

Let us learn how we can use the UPDATE clause in Oracle to set the new values for the given table columns.

Oracle UPDATE Statement

To update the value in an existing table, we use the UPDATE statement as shown in the following syntax:

Where:

1. Table_name represents the name of the table that you wish to update.
2. Column_1, column_2,…,columnN define the names of the columns that you wish to update.
3. New_value1, new_value2,…new_valueN allow you to set the new value for each column.
4. Condition is an optional clause that allows you to limit the updated rows. If you skip the conditional clause, the statement updates all the rows in the table.

Oracle Update Example

Let us look at a real-world example on how we can use the UPDATE statement in Oracle:

The provided statement creates a table to store the latest databases, the default port, the latest database version, the database type, and the programming language which are used to build the database.

We can insert some sample records as shown in the following:

NOTE: The default_port for SQLite database is set to NULL since SQLite does not have a default port number.

Resulting Table:

Oracle Update Table

Suppose we wish to update the latest_version, name, and default port for the Microsoft SQL Server. We can execute an update query as shown in the following:

The query should locate the row where the name is LIKE “Microsoft SQL Server” and change the name, default_port, and latest_version to the new values.

NOTE: The previous data is for demonstration purposes. It may not accurately reflect an up-to-date information about the databases.

Once updated, we can view the new table for data changes as follows:

As we can see, the table reflects the updated changes.

Conclusion

In this tutorial, you came across the UPDATE statement that allows you to update a single or multiple columns in a database table.

However, like most database practices, it comes with potential drawbacks. For example:

1. Performance – Performing an update on multiple columns is more time-consuming and resource-intensive than updating a single column. This becomes more significant, especially when multiplying a large number of rows with complex data.
2. Data integrity – Another concern when updating multiple columns is data integrity. If used incorrectly, updating multiple columns can lead to data corruption or loss. You can dive into various data normalization techniques to mitigate this, but it’s always good to be mindful. You can also test your update queries in development before taking them to production.
3. Query Complexity – Similarly, running the update statements can increase the complexity of your queries, making them harder to read, maintain, or debug.

In the end, updating multiple columns in an Oracle database can be useful in some situations. Still, it’s essential to consider using the best practices to minimize the risks.