SQLTeam.com | Weblogs | Forums

If exists, do not update row

tsql
sql2008
sql2008r2
#1

The following table, Approvers, has this data:

DECLARE @Approvers TABLE (ID INT, EmployeeID INT, OrderID INT)
INSERT INTO @Approvers (ID, EmployeeID, OrderID) VALUES (1, 28463, 356024)
INSERT INTO @Approvers (ID, EmployeeID, OrderID) VALUES (5, 364486, 356024)
INSERT INTO @Approvers (ID, EmployeeID, OrderID) VALUES (6, 368945, 356024)
INSERT INTO @Approvers (ID, EmployeeID, OrderID) VALUES (7, 368945, 356051)

--ID EmployeeID    OrderID
--1    28463    356024
--5    364486    356024
--6    368945    356024
--7    368945    356051

When an Employee is disabled, any entry in this table should be replaced by another on by choosing from a list. But if the new choice already exists for that OrderID, do not update (it will be disabled by another script).

     DECLARE @UserID        INT = 368945, --inactive
    @ApproverID            INT    = 364486 -- new approver

--expecting ID = 7

    SELECT ID FROM @Approvers 
    WHERE EmployeeID = @UserID
    AND NOT EXISTS (????????)

As you can see, I'm a little stuck on this and have tried several attempts but no luck.
Any input is appreciated.

#2

How do you tell that a choice is "new"?

#3

Something like this perhaps:

select a.id
  from @approvers as a
 where a.employeeid=@userid
   and not exists (select 1
                     from @approvers as b
                    where b.orderid=a.orderid
                      and b.employeeid=@approverid
                  )
;
#4

Something exactly like that. I've done things like this before but for some reason I hit a mental block!

Thanks!!