divya.trehan573 divya.trehan573 - 1 month ago 5
SQL Question

Update the first row of table via sql

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

Answer

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;