Solved

NESTED SQL

Posted on 2011-09-23
9
274 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
oops, I guess my post is the implementation of what mwvisa1 explains :)

by the way "UPDATE b ..." or "UPDATE BRenewals..." both valid...
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
Comment Utility
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
Comment Utility
@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
Comment Utility
captcha :)
0
 

Author Comment

by:StuartK
Comment Utility
You are all genius's!!!
0
 

Author Closing Comment

by:StuartK
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The purpose of this article is to demonstrate how we can use conditional statements using Python.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

762 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

6 Experts available now in Live!

Get 1:1 Help Now