Solved

Help with SQL UPDATE script

Posted on 2008-10-03
17
211 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

23 Experts available now in Live!

Get 1:1 Help Now