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

x
?
Solved

Updating row with most current date

Posted on 2011-03-09
12
Medium Priority
?
49 Views
Last Modified: 2015-04-08
I have a table with a customer numbe, order numberr , 'current indicator' and a 'create date'.
There will be multiple rows with same customer number, current indicator'  and order number.
The only difference will be create date.
I want to update current_indicator with a 'Y' on row with most current date.
Can someone do this using max function on the date column ?

0
Comment
Question by:guinnie
[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
  • 5
  • 5
  • 2
12 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35086826
update U
 set current_indicator='Y'

          from (select *
                             ,row_number() over (partition by customer_number,order_number order by create_date desc) as rn
                      from yourtable) as U
            where rn=1
              and current_indicator <>'Y'
0
 

Author Comment

by:guinnie
ID: 35086995
That didn't work. No rows returned.
Ok , i tried to simplify request but I'll give you more info.
Here is my data. First column is student_skey. Second column is create_date.

127      2010-09-25 04:08:14.000
127      2010-09-25 06:19:45.000
127      2010-10-11 23:28:29.000
127      2010-10-12 02:18:30.000
127      2010-10-16 19:26:50.000

Here is my code based on your response:

update  U
 set current_indicator='Y'
  from (select *, row_number() over
           (partition by student_skey order by create_date desc) as rn
                 from fa_school_enrollment) as U
 where rn=1
       and current_indicator <> 'N'
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35088428
try this.
update  U
 set current_indicator='Y'
  from (select *, row_number() over
           (partition by student_skey order by create_date desc) as rn
                 from @table) as U
 where rn=1
       and ISNULL(current_indicator,'N') <> 'Y'

Open in new window

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 50

Expert Comment

by:Lowfatspread
ID: 35088755
you want to change rows where the current indicator isn't 'Y' to 'Y'

if the rows currently contain an 'N' then you want this.....

update  U
 set current_indicator='Y'
  from (select *, row_number() over
           (partition by student_skey order by create_date desc) as rn
                 from fa_school_enrollment) as U
 where rn=1
       and current_indicator = 'N'


0
 

Author Comment

by:guinnie
ID: 35094012
The point is I only want to update the row with the most current date.

Not all rows.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35094751
both my and sharaths solution shoulld only be updating the student row with the latest date per student...

127      2010-09-25 04:08:14.000
127      2010-09-25 06:19:45.000
127      2010-10-11 23:28:29.000
127      2010-10-12 02:18:30.000
127      2010-10-16 19:26:50.000

so we should only affect the 2010-10-16 row for student 127,,,,

what are we missing can you provide an example with multiple students/dates and highklight what you desire to be updated...

(include the current indicator , since i'm getting confused as to what its state ie/should be.....)
0
 

Author Comment

by:guinnie
ID: 35094984
When I run the query below , the result says '0 rows affected'.

I need the current indicator set to 'Y' on the most current row.

Herre is the sql is used:
go
begin transaction
go
update  U
 set current_indicator='Y'
  from (select *, row_number() over
           (partition by student_skey order by cre_date desc) as rn
                 from fa_school_enrollment) as U
 where rn=1
       and current_indicator = 'N'
       and student_skey = '127'
                     and  SCHOOL_YEAR_code = '20102011'
go
0
 

Author Comment

by:guinnie
ID: 35096197
LowFatSpread or Sharath:


Can you see anything in SQL that was not correct ?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35096922
Can you check the current_indecator value with a SELECT query for that record?
select * 
  from (select *, row_number() over
           (partition by student_skey order by cre_date desc) as rn
                 from fa_school_enrollment) as U
 where rn=1
       and current_indicator = 'N'
       and student_skey = '127'
                     and  SCHOOL_YEAR_code = '20102011'

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 35096980
yes you need the school_year check to be "inside" the scope of the window function...

either as part of the partition clause or in the where clause...

try this...
begin transaction
go
update  U
 set current_indicator='Y'
  from (select *, row_number() over
           (partition by student_skey order by cre_date desc) as rn
                 from fa_school_enrollment
             where   student_skey = '127'
                     and  SCHOOL_YEAR_code = '20102011'
          ) as U
 where rn=1
       and current_indicator = 'N'
      
go

Open in new window

0
 

Author Comment

by:guinnie
ID: 35097129
Sharath:

Thanks for the quick reply.
I took the filters and put them in the 'partition' statement
and reran update. Then it worked perfectly. See below.
Thanks for working with me on this.

update U
 set current_indicator='Y'
          from (select *
                             ,row_number() over (partition by student_skey order by cre_date desc) as rn
                      from fa_school_enrollment
                      where student_skey = '127'
                      and  SCHOOL_YEAR_code = '20102011') as U
            where rn=1
             
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35097978
you meant Lfs?
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

688 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