SQL : Updating Oracle table with data using subquery

Share

In Oracle Database 11g , when you need to update values of certain records from one table,  and the value and the records to update are determined by a second table, you can use a subquery in the update statement to achieve the update.

Using “EMPLOYEE”  and “EMPLOYEE_STAGING”  tables as an example , we want to update field “SALARY” – table “EMPLOYEE” with the values from field  “SALARY” – table “EMPLOYEE_STAGING”.

Base data :

EMPLOYEE table :

EMPLOYEE table

EMPLOYEE_STAGING  table :

EMPLOYEE_STAGING  table

Desire Output :

EMPLOYEE table :

EMPLOYEE  table 1

Because Oracle does not allow multiple tables within the same update statement, we know at least one subquery will be needed. My first approach, which was incorrect, was this:

Incorrect Example

If we run this example, SQL Developer will report : “SQL Error: ORA-00904: “A”.”SALARY”: invalid identifier “.

The second approach that I made :

Inefficient Example

Running on SQL Developer the script had updated 3 rows from “EMPLOYEE ” table. The result is inefficient. The output was this :

Output 1Finally, I use the MERGE statement. The MERGE statement was introduced in Oracle to conditionally insert or update data depending on its presence, a process also known as an “upsert”. The MERGE statement reduces table scans and can perform the operation in parallel if required.

Efficient Example

Running on SQL Developer the script had merged 1 row from “EMPLOYEE ” table. The output was is the correct one:

Desire output

 

Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.

By continuing to use the site, you agree to the use of cookies. More information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close