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
精彩评论