Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

NESTED SQL

Posted on 2011-09-23
9
Medium Priority
?
303 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 60

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 60

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 60

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.
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…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
Suggested Courses

609 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