Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with SQL UPDATE script

Posted on 2008-10-03
17
Medium Priority
?
221 Views
Last Modified: 2012-05-05
I have data like the following:

ID          Acct          Updated
1          2002          4/4/08
2          2002          4/4/08
3          2002          9/5/08
4          2003          1/2/08
5          2003          1/2/08
6          2003          3/4/08
7          2003          4/19/08

I need to update each record where the Created date is the most recent.  There is a field called "Flag" on the table that needs to be set to 1 if it matches that.  So, the records that would get updated, in my example above, would be IDs 3 and 7.  Help?
0
Comment
Question by:saturation
[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
  • 6
  • 6
  • 4
  • +1
17 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22638062
SELECT Acct, MAX(Updated) AS MaxDate
INTO #tmp
FROM SomeTable
GROUP BY Acct

UPDATE SomeTable
SET Flag = 1
FROM SomeTable s INNER JOIN
    #tmp t ON s.Acct = t.Acct AND s.Updated = t.MaxDate

DROP TABLE #tmp
0
 
LVL 7

Expert Comment

by:Norush
ID: 22638110
Something like this?

Asuming youre want to set the flag for the last 2 record where the updated desc is most recent.
You can change the number 2 to anything you want.
update yourtablename set flag = 1
where id IN
(
select top 2 from tablename order by Updated desc
)

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 22638232
This will set a field called Active (change as necessary) to 1 if it is the newest record in YourTable (change to your real table), otherwise it will set it to 0.

;with AcctsByUpdated as
(select acct, updated, row_number() over (partition by acct order by updated desc) rn
from YourTable)
update y
set active = case when rn=1 then 1 else 0 end
from YourTable y
join AcctsByUpdated a
on y.acct = a.acct
and t.updated = a.updated
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22638237
^^ that will run for all customers at once, and update one record to active for each customer ^^
0
 
LVL 7

Expert Comment

by:Norush
ID: 22638253
update yourtablename set flag = 1
where id IN
(
select top 2 id from tablename order by Updated desc
)

This is what i ment. Syntax mistake in my first example
0
 

Author Comment

by:saturation
ID: 22638256
Oops, it looks like both examples are right, but I guess I really wanted to flag all the records that WEREN'T the most recent updated date for their account number.  So, in my example, the flagged records should be 1,2,4,5,6.    Any thoughts?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22638391
change the position of the 0 and 1 in the case.

;with AcctsByUpdated as
(select acct, updated, row_number() over (partition by acct order by updated desc) rn
from YourTable)
update y
set active = case when rn=1 then 0 else 1 end
from YourTable y
join AcctsByUpdated a
on y.acct = a.acct
and t.updated = a.updated
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22638397
I personally would avoid doing the temp table.  There are many right answers on how to do this though.
0
 

Author Comment

by:saturation
ID: 22659455
So would it be this?

with AcctsByUpdated as
(select acct, updated, row_number() over (partition by acct order by updated desc) rn
from YourTable)
update y
set active = case when rn=0 then 1 else 0 end
from YourTable y
join AcctsByUpdated a
on y.acct = a.acct
and t.updated = a.updated
0
 
LVL 7

Expert Comment

by:Norush
ID: 22659830
My suggestion would change to:

Asuming youre want to set the flag for the last 2 record where the updated desc is most recent.
You can change the number 2 to anything you want.

update yourtablename set flag = 1
where id IN
(
select top 2 id from tablename order by Updated
)

Open in new window

0
 

Author Comment

by:saturation
ID: 22659991
Thanks, but in this particular case, there might be 2 records, there might be 4 or 5, or any number.  I just need to flag all but the 1 where the updated date is the most recent where the account numbers match.
0
 
LVL 7

Expert Comment

by:Norush
ID: 22660080

update yourtablename set flag = 1
where id NOT IN
(
select top 1 id from tablename order by Updated desc
)

Open in new window

0
 

Author Comment

by:saturation
ID: 22660149
That's almost what I need, but in my example I need the account number to NOT be null and I need the query to exclude all but the most recent where the account number is the same (again, IDs 3 and 7 in my example below would NOT be flagged = 1).  Help?

ID          Acct          Updated
1          2002          4/4/08
2          2002          4/4/08
3          2002          9/5/08
4          2003          1/2/08
5          2003          1/2/08
6          2003          3/4/08
7          2003          4/19/08
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22660261
Was this not what you wanted? http:#22638391
0
 

Author Comment

by:saturation
ID: 22660501
I guess I need a little more coaching on that example.  It appears that the sub query is working, but I'm not sure about the updated part.  Does this look correct?

with AcctsByUpdated as
(select acct, updated, row_number() over (partition by acct order by updated desc) rn
from YourTable)
update y
set flag = 1 when rn != 1 then 0 else 1 end
from YourTable y
join AcctsByUpdated a
on y.acct = a.acct
and t.updated = a.updated

I'm not sure what the "then 0 else 1 end" is supposed to do.  I might not even have it right.   Help?
0
 

Author Comment

by:saturation
ID: 22661176
BrandonGalderisi.  It seemed to work.  I will go ahead and award you the points, but I have one more question.  Small refinement here, I noticed some other interesting data in the acct number field such as below:

ID          Acct          Updated
1          2002          4/4/08
2          2002          4/4/08
3          2002-C1          9/5/08
4          2003          1/2/08
5          2003          1/2/08
6          2003-C2          3/4/08
7          2003          4/19/08

They need to be grouped by their acct # regardless of whether they have the "Cxxx" attached to it.  It should be based on whether those 4 beginning numbers match.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22661477
Will there always be at least one with??


"then 0 else 1 end"

My example actually showed this.  You didn't copy it all over.

case when rn=1 then 0 else 1 end

What it's saying is that if the row number is 1 (which will be the newest record) then set "flag" to active.  If not, set it to 0.  
with AcctsByUpdated as
(select left(acct,len(acct)-charindex('-',reverse(acct))), updated, row_number() over (partition by acct order by updated desc) rn 
from YourTable)
update y
set flag = case when rn = 1 then 1 else 0 end
from YourTable y
join AcctsByUpdated a
on left(y.acct,len(y.acct)-charindex('-',reverse(y.acct)))= a.acct
and t.updated = a.updated

Open in new window

0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

715 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