Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Help with SQL UPDATE script

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
saturation
Asked:
saturation
  • 6
  • 6
  • 4
  • +1
1 Solution
 
Patrick MatthewsCommented:
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
 
NorushCommented:
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
 
BrandonGalderisiCommented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
BrandonGalderisiCommented:
^^ that will run for all customers at once, and update one record to active for each customer ^^
0
 
NorushCommented:
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
 
saturationAuthor Commented:
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
 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
I personally would avoid doing the temp table.  There are many right answers on how to do this though.
0
 
saturationAuthor Commented:
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
 
NorushCommented:
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
 
saturationAuthor Commented:
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
 
NorushCommented:

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

Open in new window

0
 
saturationAuthor Commented:
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
 
BrandonGalderisiCommented:
Was this not what you wanted? http:#22638391
0
 
saturationAuthor Commented:
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
 
saturationAuthor Commented:
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
 
BrandonGalderisiCommented:
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 6
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now