CODEWITHSUNDEEP
sqlsql-server-2014
DGriger
DGriger

Reputation: 3

How to update a row with the first value of that group if another column matches?

My issue is pretty straight forward, all of the queries we have tried error out. I am a novice user and am still learning the SQL language, any help would be very appreciated.

I'm attempting to update a table to where if the rate column matches Mat ID will update with the first value from that grouping.

+------+--------+
| Rate | Mat ID |
+------+--------+
| 1    | 81     |
| 2    | 82     |
| 2    | 83     |
| 3    | 85     |
| 2    | 86     |
| 2    | 87     |
| 3    | 88     |
+------+--------+

Expected result:

+------+--------+
| Rate | Mat ID | 
+------+--------+
| 1    | 81     |
| 2    | 82     |
| 2    | 82     |
| 3    | 85     |
| 2    | 82     |
| 2    | 82     |
| 3    | 85     |
+------+--------+

Upvotes: 0

Views: 32

Answers (1)

Chris Pickford
Chris Pickford

Reputation: 8991

Assuming the following:

  • Your definition of 'first' means the minimum value per grouping.
  • Typo on the last row in your expected results should be 85 and not 88.

The following UPDATE statement meets your requirements:

;
WITH    CTE_MinMatID
          AS (
              SELECT    Rate,
                        MIN(MatID) MinMatID
              FROM      @table
              GROUP BY  Rate
             )
    UPDATE  t
    SET     t.MatID = cte.MinMatID
    FROM    @table AS t
    INNER JOIN CTE_MinMatID cte ON cte.Rate = t.Rate;

Working example here.

Upvotes: 1

Related Questions