CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Abijith Das
Abijith Das

Reputation: 23

update all rows of a table based on minimum value of its group

I have a table like this

Date----- ----------Value--------- Group <br>
2017-01-01--------10--------------1--<br>
2017-01-02---------9---------------1--<br>
2017-01-03 --------5---------------2--<br>
2017-01-04 --------4---------------2--<br>

i want to update all value column in the table such that it is set to minimum date's value in that group

like this

Date----- ----------Value--------- Group <br>
2017-01-01--------10--------------1--<br>
2017-01-02---------10---------------1--<br>
2017-01-03 --------5---------------2--<br>
2017-01-04 --------5---------------2--<br>

Upvotes: 1

Views: 1494

Answers (3)

Ullas
Ullas

Reputation: 11556

You can also use a CTE.

Query

;with cte as(
    select [rn] = row_number() over(
        partition by [Group]
        order by [Date]
    ), *
    from [your_table_name]
)
update t1
set t1.[Value] = t2.[Value]
from cte t1
join cte t2
on t1.[Group] = t2.[Group]
and t1.[rn] > t2.[rn];

Upvotes: 0

connectedsoftware
connectedsoftware

Reputation: 7097

Here you go, 2 sub-queries, the first to calculate min date per group then join back to original table to get the associated value. Then finally join this to the original table to update all associated groups with that value:

UPDATE M SET M.Value = RESULT.Value FROM MyTable M
 INNER JOIN (
  SELECT MV.Group, M.Value FROM MyTable M
     INNER JOIN (
         SELECT MIN(Date) as MinDateValue, Group FROM MyTable
         GROUP BY Group
           ) MV ON MV.MinDateValue = M.Date AND MV.Group = M.Group
  ) RESULT ON RESULT.Group = M.Group

Upvotes: 1

Mansoor
Mansoor

Reputation: 4192

First get min date and value from sub query.Based on this result update main table

CREATE TABLE #Table(_Date Date,value INT,_Group INT)

INSERT INTO #Table(_Date ,value ,_Group)
SELECT '2017-01-01',10,1 UNION ALL
SELECT '2017-01-02',9,1 UNION ALL
SELECT '2017-01-03',5,2 UNION ALL
SELECT '2017-01-04',4,2

UPDATE #Table SET value = _Output._Value
FROM 
(
  SELECT A._Date , A._Group , T.value _Value
  FROM #Table T
  JOIN 
  (
    SELECT MIN(_Date) _Date ,_Group
    FROM #Table
    GROUP BY _Group
  ) A ON A._Date = T._Date

 ) _Output WHERE _Output._Group = #Table._Group

 SELECT * FROM #Table

Upvotes: 0

Related Questions