Home » SQL & PL/SQL » SQL & PL/SQL » Update Join View
Update Join View [message #287015] Mon, 10 December 2007 23:06 Go to next message
donind
Messages: 95
Registered: February 2007
Member
HI,

Is it possible to write a update join view for below update statement. It has all key preserved columns in where clause.


UPDATE M_DETAIL
      SET STATE = 'DEL',
          LAST__DATE = SYSDATE
      WHERE (OM_PAR_ID, 
             START_DATE) IN (SELECT OM_PAR_ID, START_DATE
                                  FROM (SELECT OM_PAR_ID, 
                                              START_DATE,
                                              ROW_NUMBER() 
                                                 OVER (PARTITION BY OM_PAR_ID
                                                    ORDER BY CREN_DTE DESC,START_DATE DESC) RANK1 
                                       FROM M_DETAIL
                                       WHERE OM_PARA_ID IN (SELECT OM_PAR_ID
                                                                 FROM M_DETAIL
                                                                 WHERE SYS_STATE = 'A'
                                                                 GROUP BY OM_PAR_ID 
                                                                 HAVING COUNT(1) > 1
                                                                )
                                      )
                                 WHERE RANK1 <> 1)
      AND STATE <> 'DEL';





Any help reallly help ful.

Thanks in advance

[Updated on: Mon, 10 December 2007 23:08]

Report message to a moderator

Re: Update Join View [message #287062 is a reply to message #287015] Tue, 11 December 2007 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.

Regards
Michel
Re: Update Join View [message #287064 is a reply to message #287062] Tue, 11 December 2007 00:52 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

can u explain me where actually the problem is? Why cant we write an update join view for above querry. Is it possible to write if it doesnt have any aggregate functions in it.

Thanks in advance
Re: Update Join View [message #287081 is a reply to message #287064] Tue, 11 December 2007 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
aggregate, analytical function, primary key... many restrictions.
See UPDATE.

Regards
Michel
Re: Update Join View [message #287089 is a reply to message #287081] Tue, 11 December 2007 01:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I think you can rewrite your query so that it does not need the group by having... . Your partition key is the same as the group key and you want only records with a rank different from 1. So groups with only one member (see your HAVING clause) are excluded automatically.

MHE
Re: Update Join View [message #287307 is a reply to message #287089] Tue, 11 December 2007 22:20 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The CBO is not very perceptive when it comes to Key Presevation. It seems to require that the key is preserved by a unique/primary key or a unique index, however it is also possible to preserve a key (or perhaps, "introduce" a key) with a GROUP BY or DISTINCT. Neither of these options may be used in an updateable join view.

Try the MERGE statement instead.

Alternatively, you could INSERT the change-data into a GLOBAL TEMPORARY TABLE that has a PK, and then use that in an updateable join view.

Ross Leishman
Previous Topic: regarding count
Next Topic: printing data into text file
Goto Forum:
  


Current Time: Wed Dec 07 09:00:24 CST 2016

Total time taken to generate the page: 0.19620 seconds