Solved

NESTED SQL

Posted on 2011-09-23
9
275 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:StuartK
  • 4
  • 3
  • 2
9 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36589439
Hi.

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 ...
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36589443
Here is an article I wrote on ranking, if you are unfamiliar with the concept:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 250 total points
ID: 36589545
if you are joining only with alarmref you should group by that column only

so try this:

UPDATE BRenewals
SET DateIssued = a.DateIssued , RenewalDate = a.RenewalDate  , ServiceLevel = a.ServiceLevel 
FROM BRenewals b  INNER JOIN
(
select * from (
SELECT *, row_number() over (partition by AlarmRef order by RenewalDate desc) rn from AService
) x where rn=1
) a ON b.AlarmRef = a.AlarmRef

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36589559
oops, I guess my post is the implementation of what mwvisa1 explains :)

by the way "UPDATE b ..." or "UPDATE BRenewals..." both valid...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
ID: 36589571
I would do it more like this to avoid double derived queries plus to clear up the ambiguity in columns in SET; however, the above is pretty much what I was suggesting using ROW_NUMBER().

UPDATE b
SET b.DateIssued = a.DateIssued 
  , b.RenewalDate = a.RenewalDate  
  , b.ServiceLevel = a.ServiceLevel 
FROM BRenewals b 
INNER JOIN (
   SELECT DateIssued, RenewalDate, ServiceLevel, AlarmRef
        , ROW_NUMBER() OVER(PARTITION BY AlarmRef ORDER BY RenewalDate DESC) RN
   FROM AService
) a ON b.AlarmRef = a.AlarmRef AND a.RN = 1
;

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36589595
@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. ;-)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36589644
captcha :)
0
 

Author Comment

by:StuartK
ID: 36590460
You are all genius's!!!
0
 

Author Closing Comment

by:StuartK
ID: 36590470
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!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now