具有多个条件的更新. SQL 2008 [英] An update with multiple conditions. SQL 2008

问题描述

我有一张桌子...

ProjectID    UserID    RoleID
101          1         10
101          2         10
102          2         10
102          3         10
103          1         10

当前只有一种类型的角色，即角色"10"，但我想添加一个新角色，即角色"11"，它将作为主角.因此，凡是具有"10"角色的用户的项目，都应具有领导作用.被选择为潜在客户的用户将基于优先级列表，在本示例中，我们将顺序定为1、2、3.

Currently there is only one type of Role, role '10', but I'm wanting to add a new role, role '11', which will act as a lead. So any project that has a user with the role of '10', should have a lead. The user chosen to be lead will be based on a priorty list, in this example we'll say the order is 1, 2, 3.

预期结果...

ProjectID    UserID    RoleID
101          1         11
101          2         10
102          2         11
102          3         10
103          1         11

推荐答案

您可以使用row_number()找出哪个用户具有最高优先级. SQL Server让您以可更新的CTE进行此操作，因此查询如下所示:

You can figure out which user has the highest priority by using row_number(). SQL Server let's you do this in an updatable CTE, so the query looks like this:

with toupdate as (
      select t.*,
             row_number() over (partition by projectid
                                order by (case when userid = 1 then 1
                                               when userid = 2 then 2
                                               when userid = 3 then 3
                                               else 4
                                          end
                                         )
                               ) as PriorityForLead
      from table t
     )
update toupdate
    set RoleId = 11
    where PriorityForLead = 1;

这篇关于具有多个条件的更新. SQL 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！