Hey All,
So this a pretty specific SQL question. SQL server allows a from clause in update statements. I'm trying to mimic this behaviour in an Oracle stored procedure, while circumventing the need for a complex routine, ex: selecting the values I need for the comparisons in the where clause into variables.
Ideally I would like to use the WITH keyword, but I haven't got this to compile. It may be possible, however.
Help meh!
Here's my code: (This doesn't compile and is intended to give you guys an idea of what I'm attempting to do)
with dups
as (select ticketid,
assetid,
Max(ticketassetid) as TicketAssetId
from ticketasset
where isactive = 1
group by ticketid,
assetid
having Count(ticketassetid) > 1)
update dbo$ticketasset
set isactive = 0
where ticketasset.ticketassetid < dups.ticketassetid
and ticketasset.ticketid = dups.ticketid
and ticketasset.assetid = dups.assetid
and ticketasset.isactive = 1;