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
Solved

Help with SQL UPDATE script

Posted on 2008-10-03
17
213 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

839 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