Oracle update sql query

Question

My query is this. It is not showing error. But having a long execution time that Iam forced to close. Please check

update trans_rec a
set a.qty = (select b.qtydes from itbg_store_delchellan b where b.icode = 
a.icode and a.controlno=b.DEL_NO AND A.controlno IS NOT NULL) 
where exists 
    (select b.qtydes from itbg_store_delchellan b where b.icode = a.icode and 
    a.controlno=b.DEL_NO  AND A.controlno IS NOT NULL )

@Zeki's below answer seems the way to go: an update based on a query with a join. — johey, Dec 03 '18 at 11:03
if you could put query plan may be we could give better answer — perseverance, Dec 03 '18 at 11:19

score 1 · Answer 1 · answered Dec 03 '18 at 11:02

You can re-write your query as follow:

UPDATE trans_rec a
SET a.qty =
    (SELECT b.qtydes 
    FROM itbg_store_delchellan b
    WHERE b.icode = a.icode and a.controlno=b.DEL_NO) 
WHERE a.controlno IS NOT NULL
AND EXISTS
(SELECT b.qtydes FROM itbg_store_delchellan b
WHERE b.icode = a.icode AND a.controlno=b.DEL_NO)

After this you must add indexes on your field search, as follow:

add following filter on these fields

b.icode
a.controlno
a.icode

score 0 · Answer 2 · answered Dec 03 '18 at 11:01

0

Try this update:

UPDATE A
SET     qty = B.qtydes 
from    trans_rec AS A
INNER JOIN itbg_store_delchellan B
             ON B.icode = A.icode 
             AND A.controlno=b.DEL_NO 
             AND A.controlno IS NOT NULL

answered Dec 03 '18 at 11:01

Zeki Gumus

1,484
7
14

The advantage of this (in addition to being simple and fast) is that you can easily test what the update is going to do: replace the UPDATE and SET with a SELECT. `SELECT A.qty, B.qtydes new_qty from trans_rec AS A INNER JOIN itbg_store_delchellan B ON B.icode = A.icode AND A.controlno=b.DEL_NO AND A.controlno IS NOT NULL ` – johey Dec 03 '18 at 11:06
You may want to check this post then: https://stackoverflow.com/a/2446834/6996150 – johey Dec 03 '18 at 11:16
Zeki Gumus: It is showing SQL querry not ended properly – user3698150 Dec 03 '18 at 11:17
This query won't work on Oracle, since Oracle does not support this syntax `update ... from ... join ...`. – krokodilko Dec 03 '18 at 11:55
I am sorry for confusing all. This query based on sql-server. I have missed to check the database provided on question. I already up-voted Joe Taras's answer which is I would offer same thing. – Zeki Gumus Dec 03 '18 at 12:24

Kaushik Nayak · Accepted Answer · 2018-12-03T11:30:18.433

0

You may use a MERGE statement in Oracle.

MERGE INTO trans_rec t USING 
( select DISTINCT icode,del_no,qtydes
       FROM itbg_store_delchellan s 
)
ON (
     s.icode = t.icode AND t.controlno = s.del_no
)
WHEN MATCHED THEN UPDATE SET t.qty = s.qtydes
WHERE t.controlno IS NOT NULL

edited Dec 03 '18 at 11:30

answered Dec 03 '18 at 11:11

Kaushik Nayak

30,772
5
32
45

ORA-30926: unable to get a stable set of rows in the source tables – user3698150 Dec 03 '18 at 11:26
@user3698150 : That's because you have multiple values in `itbg_store_delchellan` for that key. I have added a `DISTINCT` now. please check. – Kaushik Nayak Dec 03 '18 at 11:31

score 0 · Answer 4 · answered Dec 03 '18 at 11:51

To improve performance, create a composite index on itbg_store_delchellan:

create index idx_itbg_store_delchellan_3 on itbg_store_delchellan(icode, del_no, qtydes)

Note that the condition A.controlno IS NOT NULL is redundant. The correlation condition will already filter out NULL values, so you can remove this from both subqueries.

Oracle update sql query

4 Answers4