Update Join View [message #287015] |
Mon, 10 December 2007 23:06  |
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 #287064 is a reply to message #287062] |
Tue, 11 December 2007 00:52   |
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 #287307 is a reply to message #287089] |
Tue, 11 December 2007 22:20  |
rleishman
Messages: 3728 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
|
|
|