NESTED SQL
Hi, We have this sql statement
UPDATE BRenewals
SET DateIssued = a.DateIssued , RenewalDate = a.RenewalDate , ServiceLevel = a.ServiceLevel
FROM BRenewals b INNER JOIN
(SELECT DateIssued, MAX(RenewalDate) AS RenewalDate, ServiceLevel, AlarmRef
FROM AService
GROUP BY AlarmRef, DateIssued, ServiceLevel) a ON b.AlarmRef = a.AlarmRef
- We are trying to insert the max value into a table BRenewals where AlarmRef are equal for fields in question
- Focus is on the nested SELECT statement!
- It works perfectly when we remove all fields other than AlarmRef and RenewalDate in the select and the GROUP BY (i.e. it shows and inserts into BRenewals the MAX value)
- However, when uisng all the fields specified in SQL above, it basically returns records if two records exist (i.e. it doesn't doesn't show the MAX value only)
Can anybody help with this. It's so near, but we need to specify all the fields in the SELECT statement so the update has reference to them! Thought about using a temporary table to insert the SELECT statement into and then reference that temp tabke, but would rather avoid another temp table if we could!
UPDATE BRenewals
SET DateIssued = a.DateIssued , RenewalDate = a.RenewalDate , ServiceLevel = a.ServiceLevel
FROM BRenewals b INNER JOIN
(SELECT DateIssued, MAX(RenewalDate) AS RenewalDate, ServiceLevel, AlarmRef
FROM AService
GROUP BY AlarmRef, DateIssued, ServiceLevel) a ON b.AlarmRef = a.AlarmRef
- We are trying to insert the max value into a table BRenewals where AlarmRef are equal for fields in question
- Focus is on the nested SELECT statement!
- It works perfectly when we remove all fields other than AlarmRef and RenewalDate in the select and the GROUP BY (i.e. it shows and inserts into BRenewals the MAX value)
- However, when uisng all the fields specified in SQL above, it basically returns records if two records exist (i.e. it doesn't doesn't show the MAX value only)
Can anybody help with this. It's so near, but we need to specify all the fields in the SELECT statement so the update has reference to them! Thought about using a temporary table to insert the SELECT statement into and then reference that temp tabke, but would rather avoid another temp table if we could!
Here is an article I wrote on ranking, if you are unfamiliar with the concept:
https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html
https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
oops, I guess my post is the implementation of what mwvisa1 explains :)
by the way "UPDATE b ..." or "UPDATE BRenewals..." both valid...
by the way "UPDATE b ..." or "UPDATE BRenewals..." both valid...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@HainKurt, regarding:
by the way "UPDATE b ..." or "UPDATE BRenewals..." both valid...Since it is valid syntax to do UPDATE tbl1 ... FROM tbl2 ... without specifying tbl1 in the FROM clause, the UPDATE not using the alias is a bit untrustworthy because BRenewals and b are two different tables at that point. Therefore, my suggestion holds at using "b" if aliased as such in the FROM. ;-)
captcha :)
ASKER
You are all genius's!!!
ASKER
My apolgies, you are both genius's, and I thnka you so much! I've learnt a little more thanks to that! One day I might be gods like you too!
Are you wanting the DateIssued and ServiceLevel corresponding to the row with the MAX(RenewalDate)? If so, ROW_NUMBER() OVER(PARTITION BY AlarmRef ORDER BY RenewalDate DESC) RN may serve you better. That will rank all the rows within an AlarmRef with the MAX(RenewDate) row being RN = 1.
If on the other hand, all three fields must equal, then you have to ensure that all three fields are compared in the ON clause as part of the JOIN.
As an aside, you should use the alias "b" as the target of the update, i.e., UPDATE b ...