0
Gid Rid Cid PriorityId

1   7   11  NULL
1   7   12  NULL
1   8   13  NULL
1   9   25  NULL
1   13  18  NULL
1   13  19  NULL
1   14  23  NULL
1   14  24  NULL

I need to set the priorityId to the following. Can anyone suggest an sql update statement. I think I would need a group by Rid and Gid or self join

Gid Rid Cid PriorityId

1   7   11  1
1   7   12  2
1   8   13  1
1   9   25  1
1   13  18  1
1   13  19  2
1   14  23  1
1   14  24  2

Thank you

user575219
  • 2,346
  • 15
  • 54
  • 105
  • What happened when you attempted to group by rid and gid, or when you tried to update by joining the table to itself? Did you not see the results you expected ? – Hunter McMillen Jun 16 '14 at 19:58
  • 2
    what version of SQL? mySQL, SQL Server 2012, Oracle 11g? This looks like it could be handled by the window concept involved in using the `over` syntax in SQL server or Oracle. Something like: http://stackoverflow.com/questions/14359749/how-to-return-a-incremental-group-number-per-group-in-sql – xQbert Jun 16 '14 at 20:00
  • Is `Cid` important? And what flavour of SQL are you using? – Siyual Jun 16 '14 at 20:22

2 Answers2

1

For SQL Server 2012, you can use the following using an Over statement with Gid and Rid as the partitions:

Update  T
Set     PriorityId = D.PriorityId
From    
(   
    Select  Gid, Rid, Cid,
            Row_Number() Over (Partition By Gid, Rid Order By Gid, Rid, Cid) As PriorityId
    From    Table
) D
Join    Table   T   On  T.Gid = D.Gid 
                    And T.Rid = D.Rid 
                    And T.Cid = D.Cid

The inner statement below should show you the results without updating the table:

Select  Gid, Rid, Cid,
        Row_Number() Over (Partition By Gid, Rid Order By Gid, Rid, Cid) As PriorityId
From    Table
Siyual
  • 16,415
  • 8
  • 44
  • 58
  • I tried update u set u.priorityid = T1.RowFilter FROM Table1 u Inner Join (SELECT groupid, ruleid, DENSE_RANK() OVER (ORDER BY groupid, ruleid) AS RowFilter FROM Table1 hl ORDER BY RowFilter ) As T1 ON u.gid= T1.gid and u.rid= T1.rid. Didnt seem to do the rank. So I needed row_number instead of dense_rank. And orderby ConnectionID – user575219 Jun 16 '14 at 20:42
  • +1 . . . This is a good answer. There is, however, an easier way to write the update. See my answer. – Gordon Linoff Jun 16 '14 at 21:06
1

I would write the update like this:

with toupdate as (
    Select t.*
           Row_Number() Over (Partition By Gid, Rid Order By Gid, Rid, Cid) As new_PriorityId
    From Table t
   )
update toupdate
    set PriorityId = new_PriorityID;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786