开发者

SQL Server 2005 conditional update of a field

I have the SQL below and want to update the managerNTID with @managerNTID only if the users DB record has StopManagerOverride value of 0, if it is set to 1 on the record then I don't want to update this field:

UPDATE ee
SET 
    MangerId        = CASE ee.ShopManagerOverride
                            WHEN 0 THEN @MangerId
           开发者_如何学编程                 ELSE ee.MangerId
                          END
    ,ManagerNTID        = CASE ee.ShopManagerOverride
                            WHEN 0 THEN @managerNTID
                            ELSE ee.ManagerNTID
                          END
    ,NTID               = @NTID
    ,FirstName          = @FirstName
    ,LastName           = @LastName
    ,FullName           = @FullName
    ,ReportingGroup     = @ReportingGroup
    ,DistinguishedName  = @DistinguishedName
    ,IsActive           = 1
    --,StopManagerOverride= 1
    ,LastUpdate         = GETDATE()
    ,UpdateBy           = @UpdateBy
FROM dbo.Employees ee
    Inner Join dbo.Employees e ON e.NTID = ee.NTID
WHERE ee.NTID = @NTID

Thoughts on how to make this one field in the update have an inline condition?

edit: i am almost there, just need to resolve some ambiguous columns now. my eyes are red from this already.


You could try something like this - if the ShopManagerOverride is set to 1, update it to the parameter value passed in, otherwise update it to the value it already has (I'm also assuming your field is really ManagerId - not MangerId - right??):

UPDATE 
    dbo.Employees
SET 
    ManagerId = @ManagerId,
    ManagerNTID = CASE e.ShopManagerOverride
                     WHEN 1 THEN @managerNTID
                     ELSE e.ManagerNTID
                  END,
    NTID = @NTID,
    FirstName = @FirstName,
    LastName = @LastName,
    FullName = @FullName,
    ReportingGroup = @ReportingGroup,
    DistinguishedName = @DistinguishedName,
    IsActive = 1,
    LastUpdate = GETDATE(),
    UpdateBy = @UpdateBy
FROM
    dbo.Employees e 
WHERE 
    e.NTID = dbo.Employees.NTID
    AND dbo.Employees.NTID = @NTID

Untested, straight and "free hand" from my brain - hope I got the syntax right! Try it - does it work, does it do what you're looking for??


I've been wondering how to do this also. I've seen Case statements but I don't know if that would work in this situation. How I've done it in the past is with copy and paste and then go like this

if (conditional)
Begin
Update statement with field
End
Else
Begin
update statement without field
End

Ok Try something like this, I'm not sure if this works but it gives you a direction to try in

UPDATE Employees
SET 
     MangerId           = @MangerId
    ,ManagerNTID        = @managerNTID
    ,NTID               = @NTID
    ,FirstName          = @FirstName
    ,LastName           = @LastName
    ,FullName           = @FullName
    ,ReportingGroup     = @ReportingGroup
    ,DistinguishedName  = @DistinguishedName
    ,IsActive           = 1
    ,StopManagerOverride= CASE Conditional WHEN True THEN (value to set it to)
     ELSE (pre existing value of the row) END;

    ,LastUpdate         = GETDATE()
    ,UpdateBy           = @UpdateBy
WHERE NTID = @NTID


Another Way is

 If StopManagerOverride = 0
 Begin
 Set @ManagerNTID = Select Existing Value of the row
 END

THen Continue with the normal update statement

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜