Solved

Help with SQL UPDATE script

Posted on 2008-10-03
17
212 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
  • 6
  • 6
  • 4
  • +1
17 Comments
 
LVL 92

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 500 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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 Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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