Home » SQL & PL/SQL » SQL & PL/SQL » Merge Problem
Merge Problem [message #292415] Tue, 08 January 2008 22:17 Go to next message
ashikin_858
Messages: 6
Registered: November 2007
Junior Member
Hi all;

I have a problem executing the merge statement.Hope u guys can help...

I need to update multiple rows based on the same acct_no using the merge statement.

Lets say i have 3 entries for acct_no 'D929731760106'
For first row i need to update the txn_amt with the sum of all amt for acct_no 'D929731760106'.

Other than first row (acct_no 'D929731760106') i need to update its status_code ='TDP'

I try this query but it throws error saying "ORA-00905:missing keyword" .But when i delete the lines 'WHEN NOT MATCHED THEN' it works ok.

merge into gfmas_inbnd_txn tab_1 using
(select sum(txn_amt) sum_amt from 
(select t1.TXN_ID_NUM,t1.txn_amt from gfmas_inbnd_txn t1 where acct_no='D929731760106') a
) b
ON (ROWNUM=1)
WHEN MATCHED THEN
UPDATE SET tab_1.txn_amt=b.sum_amt
WHEN NOT MATCHED THEN
UPDATE SET tab_1.status_code='TDP'


Someone pls help... Embarassed
TQ in advance
Re: Merge Problem [message #292482 is a reply to message #292415] Wed, 09 January 2008 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not a merge, this just one or 2 updates.
A merge is insert or update, not update or update.

Regards
Michel
Re: Merge Problem [message #292507 is a reply to message #292482] Wed, 09 January 2008 01:51 Go to previous messageGo to next message
ashikin_858
Messages: 6
Registered: November 2007
Junior Member
Tq Michel for the info;

I dont know that merge cannot use 2 updates

Do u have any better solution for this?...Tq
Re: Merge Problem [message #292514 is a reply to message #292507] Wed, 09 January 2008 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to better define your specification:
- which one is first?
- what happen to status for "first" row?
- what happen to txn_amt for "non-first" rows?

Regards
Michel
Re: Merge Problem [message #292527 is a reply to message #292514] Wed, 09 January 2008 02:28 Go to previous messageGo to next message
ashikin_858
Messages: 6
Registered: November 2007
Junior Member

Ok Michel;

This is my better spec Smile

u ask : which one is first?
-->when the rownum =1

u ask :what happen to status for "first" row?
-->do nothing (no update on the status_code for rownum=1),only update the txn_amt

u ask :what happen to txn_amt for "non-first" rows?
-->do nothing on txn_amt of "non-first" rows , but need to update the status_code (of "non-first" rows) ='TDP'


TQ
Hope u can help





Re: Merge Problem [message #292530 is a reply to message #292527] Wed, 09 January 2008 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

u ask : which one is first?
-->when the rownum =1

This means you don't care.
Do you confirm?

Regards
Michel
Re: Merge Problem [message #292533 is a reply to message #292530] Wed, 09 January 2008 02:56 Go to previous messageGo to next message
ashikin_858
Messages: 6
Registered: November 2007
Junior Member

Yes , it actually doesnt matter which transaction need to update the txn_amt but only one need to be updated (txn_amt) and the rest only update the status (for same acct_no)

in this case i think is better to know which to update the txn_amt and which to update the status_code, that is why i specify the rownum =1 to update the txn_amt
and for rownum != 1 update the status_code
Re: Merge Problem [message #292534 is a reply to message #292527] Wed, 09 January 2008 02:56 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Who is 'u'?

Please do not use IM speak on these forums. It often makes it difficult to understand what you are trying to say.
i.e.
When you mean you, please don't use u.
When you mean please, please don't use plz
When you mean you are, you're or your please don't use ur
etc. etc.
Thanks
Re: Merge Problem [message #292541 is a reply to message #292533] Wed, 09 January 2008 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So we will take the one that has the min rowid.
drop table t purge;
create table t (id integer, val integer, status varchar2(10));
insert into t values (1,10,'');
insert into t values (1,15,'');
insert into t values (1,20,'');
insert into t values (2,100,'');
commit;
SQL> select * from t;
        ID        VAL STATUS
---------- ---------- ----------
         1         10
         1         15
         1         20
         2        100

4 rows selected.

We first have to find the rows we want to update and the interesting values: sum(val) and min(rowid):
SQL> select rowid rid, min(rowid) over () minrid,
  2         val, sum(val) over () sumval, status 
  3  from t 
  4  where id = 1
  5  /
RID                MINRID                    VAL     SUMVAL STATUS
------------------ ------------------ ---------- ---------- ----------
AAAM8YAAEAAACs/AAA AAAM8YAAEAAACs/AAA         10         45
AAAM8YAAEAAACs/AAB AAAM8YAAEAAACs/AAA         15         45
AAAM8YAAEAAACs/AAC AAAM8YAAEAAACs/AAA         20         45

3 rows selected.

Then we have to find the query that will give us the correct values:
SQL> select decode(rid, minrid, sumval, val),
  2         decode(rid, minrid, status, 'TDP')
  3  from (select rowid rid, min(rowid) over () minrid,
  4        val, sum(val) over () sumval, status 
  5        from t 
  6        where id = 1)
  7  /
DECODE(RID,MINRID,SUMVAL,VAL) DECODE(RID
----------------------------- ----------
                           45
                           15 TDP
                           20 TDP

3 rows selected.

Finally we have to insert these values in the UPDATE statement correlating this query with the appropriate rows, this is the purpose of rowid:
SQL> update t a
  2  set (val, status) = 
  3      (select decode(rid, minrid, sumval, val),
  4              decode(rid, minrid,status,'TDP')
  5       from (select rowid rid, min(rowid) over () minrid,
  6                    val, sum(val) over () sumval, status 
  7             from t 
  8             where id = 1) b
  9       where b.rid = a.rowid)   
 10  where id = 1
 11  /

3 rows updated.

SQL> select * from t;
        ID        VAL STATUS
---------- ---------- ----------
         1         45
         1         15 TDP
         1         20 TDP
         2        100

4 rows selected.

Regards
Michel

[Updated on: Wed, 09 January 2008 03:19]

Report message to a moderator

Re: Merge Problem [message #292548 is a reply to message #292415] Wed, 09 January 2008 03:28 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
please send the complete data.
Re: Merge Problem [message #292561 is a reply to message #292541] Wed, 09 January 2008 03:49 Go to previous message
ashikin_858
Messages: 6
Registered: November 2007
Junior Member
Thanks Michel
It works!..i really appreaciate it Surprised
Previous Topic: Is select a DML statement?
Next Topic: DBMS_METADATA to get dependent DDL
Goto Forum:
  


Current Time: Sat Dec 03 01:18:46 CST 2016

Total time taken to generate the page: 0.05135 seconds