?
Solved

NESTED SQL

Posted on 2011-09-23
9
Medium Priority
?
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 60

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 60

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 58

Assisted Solution

by:HainKurt
HainKurt earned 1000 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 58

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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 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 60

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 58

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.
Suggested Courses

764 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