Why does this UPDATE fail with a unique key constraint violation?

I'm an "accidental" DBA, relatively inexperienced and baffled by this problem.

Running MS SQL Server 2012. The problem is with this UPDATE statement:

UPDATE dbo.tAccts SET
       Ticket               = 'ARP.ExGE'
       , Method             = 'smtp'
       , AcctOwner          = 'r00417819'
       , DisplayName = '~AppLight HBSFax-Inactive'
       , Destination = 'r00417819@mail.ad.ge.com'
       , UpdatedBy          = SYSTEM_USER
       , UpdatedOn          = CAST(GetDate() AS DATE)
FROM dbo.vReclaimable
WHERE OHR_EmpStatus <> 'A'

Which should update only the rows in the tAccts table which are returned by the vReclaimable view.

The vReclaimable view is based on the tAccts table and returns a subset of the rows in tAccts.

When I run it, it fails with a unique key error:

(0 row(s) affected)
Msg 2627, Level 14, State 1, Line 67
Violation of UNIQUE KEY constraint 'UQ__tAccounts_DNIS.Method.Destination.Phones'. Cannot insert duplicate key in object 'dbo.tAccts'. The duplicate key value is (68497, smtp, r00417819@mail.ad.ge.com, 800-905-8793, none).
The statement has been terminated.

Fair enough, the tAccts table does have a unique key constraint:

CONSTRAINT [UQ__tAccounts_DNIS.Method.Destination.Phones] UNIQUE NONCLUSTERED 
(
                [DNIS] ASC,[Method] ASC,[Destination] ASC,[Phone_TF] ASC,[Phone_Local] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

But here’s the weird thing. If I run these two queries:

select 'tAccts table', dnis, method, destination, phone_tf, phone_local from tAccts where dnis=68497
select 'vReclaimable view', dnis, method, destination, phone_tf, phone_local, daysidle from vReclaimable where dnis=68497

The first returns two rows (as expected):

```
(No column name) dnis method destination phone_tf phone_local
tAccts table 68497 ftp ftp://faxuser@ap1plm02cige/appliances 800-905-8793 none
tAccts table

It boils down to what UPDATE statement does. It's not entirely obvious but your statement is equivalent to this one:

UPDATE upd SET
         Ticket             = 'ARP.ExGE'
       , Method             = 'smtp'
       , AcctOwner          = 'r00417819'
       , DisplayName = '~AppLight HBSFax-Inactive'
       , Destination = 'r00417819@mail.ad.ge.com'
       , UpdatedBy          = SYSTEM_USER
       , UpdatedOn          = CAST(GetDate() AS DATE)
FROM 
    dbo.tAccts AS upd 
  CROSS JOIN
    dbo.vReclaimable AS v
WHERE OHR_EmpStatus <> 'A' ;

Since there is no mention of the dbo.tAccts table in the FROM and no join or where condition between the table and the view, it results in a CROSS join and an attempt to update all rows of the table (and not just from the view), and probably multiple times as well!

You can add a join (or where) condition with:

UPDATE upd SET
         Ticket             = 'ARP.ExGE'
       , Method             = 'smtp'
       , AcctOwner          = 'r00417819'
       , DisplayName = '~AppLight HBSFax-Inactive'
       , Destination = 'r00417819@mail.ad.ge.com'
       , UpdatedBy          = SYSTEM_USER
       , UpdatedOn          = CAST(GetDate() AS DATE)
FROM 
    dbo.tAccts AS upd 
  JOIN
    dbo.vReclaimable AS v
      ON v.PK = upd.PK              -- whatever the PK column is
WHERE OHR_EmpStatus <> 'A' ;

or (using your version):

UPDATE dbo.tAccts SET
       Ticket               = 'ARP.ExGE'
       , Method             = 'smtp'
       , AcctOwner          = 'r00417819'
       , DisplayName = '~AppLight HBSFax-Inactive'
       , Destination = 'r00417819@mail.ad.ge.com'
       , UpdatedBy          = SYSTEM_USER
       , UpdatedOn          = CAST(GetDate() AS DATE)
FROM dbo.vReclaimable
WHERE OHR_EmpStatus <> 'A'
  AND vReclaimable.PK = tAccts.PK ;

Alternatively, you can (probably) simply update the view. For this to be working, the view has to conform with the limitations about "Updatable Views". See the relevant paragraph at MSDN documentation: CREATE VIEW, Updatable Views:

UPDATE dbo.vReclaimable SET
       Ticket               = 'ARP.ExGE'
       , Method             = 'smtp'
       , AcctOwner          = 'r00417819'
       , DisplayName = '~AppLight HBSFax-Inactive'
       , Destination = 'r00417819@mail.ad.ge.com'
       , UpdatedBy          = SYSTEM_USER
       , UpdatedOn          = CAST(GetDate() AS DATE)

WHERE OHR_EmpStatus <> 'A' ;

StackExchange Database Administrators Q#123266, answer score: 18