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.
