Update the first row of table via sql

Question

I have a base table per_name :

start_date   end_date        type      name person_id 



01-jan-2016  02-Feb-2016      A         abc     1

03-Feb-2016  31-Dec-412       E         abc     1

Per_leg_data

start_date     end_date       leg_code    person_id 

31-Nov-2015    03-Mar-2016     SG            1

04-Mar-2016    31-Dec-4712     CG            1

Now what i want is that whenever the min(start_date) of name <>min(start_date) of leg_data then the first row of per_leg_data should be replaced by per_name min(start_date)

Something like :

If min(name.start_date) <> min(leg.start_date) 

then

update per_leg_data

set start_date = (select distinct min(start_date)

 from per_name pn 

where pn.person_id=person_id)

But this updating all the rows of the per_leg_data of date_start

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

Something like this is what you need. please apologize if there is an type or something as I write it in notepad++.

with T as --This table contain only the min datetime of each person.
(
    select n.person_id, min(n.start_date) personMinDate, min(l.start_date) legMinDate
    from per_name n
    join per_leg_data l on l.person_id = n.person_id
    group by n.person_id
)
update leg

  -- this part replace your leg start date with your person min date
  set leg.start_date = T.personMinDate 

from per_leg_data leg

-- this join will give you only the correct row of the leg record.
join T on T.person_id = leg.person_id and T.legMinDate = leg.start_date 

-- this filter make this is only executed when necessary.
where leg.start_date <> T.personMinDate;

Update the first row of table via sql

Sign up