Why I Like The Merge Statement


Over the past few years, I’ve stopped using the Update statement, and  started using the Merge statement for updates instead.  While many use it to do both inserts and updates at the same time, you can use Merge for only updates, or inserts.

Say I want to update some fields in the SCOTT schema.  Traditionally, I would first do the analysis by looking at the data.

Select
e.EMPNO
,e.ENAME
,e.JOB
,e.SAL
,e.COMM
,e.DEPTNO
,d.DNAME
,d.LOC
From emp  e
Join   dept d
On e.DEPTNO = d.DEPTNO
Where d.dname = ‘SALES’
And e.comm is not null

EMPNO ENAME      JOB              SAL       COMM     DEPTNO DNAME          LOC
———- ———- ——— ———- ———- ———- ————– ————-
7499 ALLEN      SALESMAN        1600        375         30 SALES          CHICAGO
7844 TURNER     SALESMAN        1500          0         30 SALES          CHICAGO
7654 MARTIN     SALESMAN        1250       1750         30 SALES          CHICAGO
7521 WARD       SALESMAN        1250        625         30 SALES          CHICAGO

4 rows selected.

Eventually, I get a Select statement showing the exact rows I want to update.

If you know that the deptno was 30, you could use the traditional update statement in this way:

Update emp
Set comm = comm  * 1.25
Where comm is not null
And deptno = 30
/

4 rows updated.

Or, if you wanted to do a subquery.

Update emp
Set comm = comm  * 1.25
Where comm is not null
And deptno in
(select deptno
from Dept
where dname = ‘SALES’
)

or

Update emp e
Set comm = comm  * 1.25
Where comm is not null
And exists
(select deptno
from Dept d
where e.deptno = d.deptno
and dname = ‘SALES’
)

The Select needs to be rewritten into an Update statement.  You need to get exactly the same Where clause.

In a lot of systems, things can get complex, and you might actually have to write PLSQL code to find different values and store them to multiple variables.  That is, instead of using a single SQL statement.

Of course, the classic error with an update statement is not to use the correct Where clause, or no Where clause at all.

Update emp
Set comm = comm  * 1.25

14 rows updated.

This will update all rows in the table.  Probably not what you want to do.

————————

Using the Merge statement, you can take the Select statement you used in your  analysis, and put it directly into the Merge statement.

You can include as many fields as you like in the Select clause, including fields that are not part of the primary key, or Where clause.

You can also include multiple tables in the Select clause.  In this case, Dept and Emp.

Merge into emp e
Using
(
Select
e.EMPNO
,e.ENAME
,e.JOB
,e.MGR
,e.HIREDATE
,e.SAL
,e.COMM
,e.DEPTNO
,d.DNAME
,d.LOC
From emp  e
Join   dept d
On e.DEPTNO = d.DEPTNO
Where d.dname = ‘SALES’
And e.comm is not null
)  src
on   ( e.empno = src.empno)
When matched then update
Set e.comm = src.comm * 1.25

4 rows merged.

But the big advantage that I like, is that the Merge statement forces you to do a clean join.

The join condition between the Select clause, and the table you are updating, MUST be correct.

For example, if you use a select statement that would give a cartesian product ….

Select
e.EMPNO
,e.ENAME
,e.JOB
,e.MGR
,e.HIREDATE
,e.SAL
,e.COMM
,e.DEPTNO
,d.DNAME
,d.LOC
From emp  e
,   dept d
Where e.comm is not null

7844 TURNER     SALESMAN        7698 1981-SEP-08 00:00:00                1500          0
30 ACCOUNTING     NEW YORK

7844 TURNER     SALESMAN        7698 1981-SEP-08 00:00:00                1500          0
30 RESEARCH       DALLAS

7844 TURNER     SALESMAN        7698 1981-SEP-08 00:00:00                1500          0
30 SALES          CHICAGO

7844 TURNER     SALESMAN        7698 1981-SEP-08 00:00:00                1500          0
30 OPERATIONS     BOSTON

16 rows selected.

Merge into emp e
Using
(
Select
e.EMPNO
,e.ENAME
,e.JOB
,e.MGR
,e.HIREDATE
,e.SAL
,e.COMM
,e.DEPTNO
,d.DNAME
,d.LOC
From emp  e
,   dept d
Where e.comm is not null
)  src
on   ( e.empno = src.empno)
When matched then update
Set e.comm = src.comm * 1.25

*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables

When the rows don’t line up exactly, you get the unstable set of rows error.

So, it forces you to do your data analysis and line the rows up correctly.  I have found this to be very useful.  Especially when you are dealing with bizarre datasets that don’t have a primary, or even an effective key.

Once this helped to catch an error that would otherwise have gone unnoticed, and too many rows would have been updated.  Good one!

Hope this is useful.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: