Correlated Update

[nvnsrt]

Hi all.

Can anybody suggest an Oracle equivalent to the following sql query :



UPDATE Profile
SET LastSavedSearch =
(
SELECT TOP 1 ss.SavedSearch--, MAX(DATEADD(s, ssb.StartTime, ssb.StartDate))
FROM Profile p
INNER JOIN ProfileFile pf ON pf.ProfileFile = p.ProfileFile
INNER JOIN SavedSearchLoadScheduleItem lsi ON lsi.DmsId = pf.DmsId
INNER JOIN SavedSearchLoadSchedule ls ON ls.SavedSearchLoadScheduleItem = lsi.SavedSearchLoadScheduleItem
INNER JOIN SavedSearchBulk ssb ON ssb.SavedSearchBulk = ls.SavedSearchBulk
INNER JOIN SavedSearch ss ON ss.SavedSearch = ssb.SavedSearch
WHERE (p.LastSavedSearch IS NULL) AND (ss.ProfileDms = p.ProfileDms)
AND (p.Profile = p1.Profile)
GROUP BY ss.SavedSearch
ORDER BY MAX(cast((ssb.StartDate + ssb.StartTime/86400) as timestamp)) DESC
)
FROM Profile p1
WHERE p1.LastSavedSearch IS NULL;



Thanks and regards,
Naveen

[Dave44]

Hi all.

Can anybody suggest an Oracle equivalent to the following sql query :



UPDATE Profile
SET LastSavedSearch =
(
SELECT TOP 1 ss.SavedSearch--, MAX(DATEADD(s, ssb.StartTime, ssb.StartDate))
FROM Profile p
INNER JOIN ProfileFile pf ON pf.ProfileFile = p.ProfileFile
INNER JOIN SavedSearchLoadScheduleItem lsi ON lsi.DmsId = pf.DmsId
INNER JOIN SavedSearchLoadSchedule ls ON ls.SavedSearchLoadScheduleItem = lsi.SavedSearchLoadScheduleItem
INNER JOIN SavedSearchBulk ssb ON ssb.SavedSearchBulk = ls.SavedSearchBulk
INNER JOIN SavedSearch ss ON ss.SavedSearch = ssb.SavedSearch
WHERE (p.LastSavedSearch IS NULL) AND (ss.ProfileDms = p.ProfileDms)
AND (p.Profile = p1.Profile)
GROUP BY ss.SavedSearch
ORDER BY MAX(cast((ssb.StartDate + ssb.StartTime/86400) as timestamp)) DESC
)
FROM Profile p1
WHERE p1.LastSavedSearch IS NULL;



Thanks and regards,
Naveen

Ok first since there are no table scripts here i have little knowledge of your tables and since there is no requirement given i can only assume that your query worked in some other environment.

Given the above caveat, I think this is in line with what you were doing:

Update profile p
set  lastsavedsearch = (select max(ss.savedsearch)
                        from   profilefile pf,
                               INNER JOIN SavedSearchLoadScheduleItem lsi ON lsi.DmsId = pf.DmsId
                               INNER JOIN SavedSearchLoadSchedule ls ON ls.SavedSearchLoadScheduleItem = lsi.SavedSearchLoadScheduleItem
                               INNER JOIN SavedSearchBulk ssb ON ssb.SavedSearchBulk = ls.SavedSearchBulk
                               INNER JOIN SavedSearch ss ON ss.SavedSearch = ssb.SavedSearch
                        where  p.profiledms = ss.profiledms
                        and    pf.ProfileFile = p.ProfileFile )
where p.lastsavedsearch is null
 

Also just to note. I havent seen an instance yet where you need to sort a sub query in an update.

let me know how this works