i am trying to update the end date in a coulmn based on the most recent start date.
id Start date End date
so i would want the last rows end date to be 10-02-2006 1 day less than the start date of the first row.
but i dont want to update the first row as i want to leave it open
code as follows
fsc.address_usages.aus_end_date = ( SELECT
( TENANCIES.TCY_REFNO=TENANCY_INSTANCES.TIN_TCY_REFN O )
AND ( TENANCY_INSTANCES.TIN_HOP_REFNO=HOUSEHOLD_PERSONS. HOP_REFNO )
AND ( HOUSEHOLD_PERSONS.HOP_PAR_REFNO=FSC.PARTIES.PAR_RE FNO )
AND ( FSC_ADDRESS_USAGES_PARTIES.AUS_PAR_REFNO=FSC.PARTI ES.PAR_REFNO )
FSC_ADDRESS_USAGES_PARTIES.AUS_AUT_FAR_CODE = 'CONTACT' )
AND FSC_ADDRESS_USAGES_PARTIES.aus_end_date IS NULL
I would personally avoid rbackmann's cursor-based solution because doing everything in a single SQL statement always results in better performance than cursor loops.
Could you be clearer on what you call "the first row" ? Is it, among the rows candidate to the update, the row which has the max start_date, as it seem to appear in your solution ? BTW it seems like your query is not complete : I can't see where your subselect ends and I don't see the where clause of your UPDATE... Could you post the whole query ?
As I see it, one solution would be like :
set end_date = (select max(start_date) - 1 from ...)
where end_date is null
and start_date <> (select max(start_date) from ...);
HTH & Regards,
ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .