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 68497 unc \\\\for4as01applge\\cfs_portfolio\\cfs_faxdocs 800-905-8793 none
and the second returns 0 rows (as expected).
If “FROM vReclaimable WHERE OHR_EmpStatus <> ‘A’” returns 0 rows, why is the UPDATE trying to update the row where DNIS=68497?
(I hope I've described this adequately. I've a feeling I'm missing something obvious)
USE [TEST-GEAFax_arley_NEW]
GO
/****** Object: Table [dbo].[tAccts] Script Date: 12/9/2015 1:39:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tAccts](
[Ticket] [varchar](30) NOT NULL,
[Method] [varchar](15) NOT NULL,
[AcctOwner] [varchar](15) NOT NULL,
[DisplayName] [varchar](75) NOT NULL,
[Destination] [varchar](75) NOT NULL,
[DNIS] [varchar](20) NOT NULL,
[DNIS2] [varchar](20) NULL,
[Phone_TF] [varchar](30) NOT NULL,
[Phone_Local] [varchar](30) NOT NULL,
[Phone_PBX] [varchar](255) NOT NULL,
[UpdatedBy] [varchar](50) NOT NULL,
[UpdatedOn] [date] NOT NULL,
[FaxNotes] [varchar](255) NULL,
[TelcomNotes] [varchar](255) NULL,
[AcctID] [int] IDENTITY(0,1) NOT NULL,
CONSTRAINT [PK__tAccounts_AcctID] PRIMARY KEY CLUSTERED
(
[AcctID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
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) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
---------------------------------------------------------------------------------
USE [TEST-GEAFax_arley_NEW]
GO
/****** Object: View [dbo].[vReclaimable] Script Date: 12/9/2015 1:39:57 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/***********************************************************************
* Written By : N. Arley Dealey (200018252
* Written On :
* Updated By :
* Updated On :
* Description : Returns data from tAccts, vRxAl, vWLT_AllGE
* Notes :
***********************************************************************/
CREATE VIEW [dbo].[vReclaimable] AS
SELECT
a.Ticket
, a.Method
, a.AcctOwner
, a.DisplayName
, a.Destination
, a.DNIS
, a.DNIS2
, a.Phone_TF
, a.Phone_Local
, a.Phone_PBX
, a.UpdatedBy
, a.UpdatedOn
, a.FaxNotes
, a.TelcomNotes
, a.AcctID
, COUNT(jt.JobID) AS 'FaxesRcvd'
, CAST(MIN(jt.TimeStamp_UTC) AS DATE) AS 'FirstRcvd'
, CAST(MAX(jt.TimeStamp_UTC) AS DATE) AS 'LastRcvd'
, DATEDIFF(dd, MAX(jt.TimeStamp_UTC), GETDATE()) AS 'DaysIdle'
, o.OHR_EmpSSO
, o.OHR_EmpStatus
, o.OHR_EmpName
, o.OHR_EmpTitle
, o.OHR_BizIndustryGroup
, o.OHR_BizSegment
, o.OHR_BizUnit
, o.OHR_BizDept
, o.OHR_BizDomain
FROM
dbo.tAccts AS a
LEFT OUTER JOIN dbo.tAccts_Retain AS r ON (a.AcctID = r.AcctID)
LEFT OUTER JOIN dbo.vWLT_AllGE AS o ON (a.AcctOwner = o.OHR_EmpSSO)
LEFT OUTER JOIN dbo.vRxAll AS jt ON (a.DNIS = jt.DNIS)
WHERE ( 1 -- place holder, has no effect
AND r.RetainID IS NULL -- out of scope: in Retain table
AND a.Method = 'smtp' -- out of scope: ftp, unc, cifs, printers
AND a.Phone_Local NOT LIKE '216-%' -- out of scope: NELA numbers
AND a.AcctOwner <> 'r00417819' -- out of scope: reclaimed numbers
AND a.AcctOwner <> 'r00336832' -- out of scope: never assigned numbers
AND a.AcctOwner <> 'r00971729' -- out of scope: invalid numbers
AND a.Destination NOT LIKE 'g%@mail.ad.ge.com' -- out of scope: distribution lists
AND a.Destination NOT LIKE 'r%@mail.ad.ge.com' -- out of scope: shared mailboxes
)
GROUP BY
a.DNIS
-- remaining columns are just for syntax reasons
, a.Ticket, a.Method, a.AcctOwner, a.DisplayName, a.Destination, a.DNIS2, a.Phone_TF, a.Phone_Local, a.Phone_PBX, a.UpdatedBy, a.UpdatedOn, a.FaxNotes, a.TelcomNotes, a.AcctID
, o.OHR_EmpSSO, o.OHR_EmpStatus, o.OHR_EmpName, o.OHR_EmpTitle
, o.OHR_BizIndustryGroup, o.OHR_BizSegment, o.OHR_BizUnit, o.OHR_BizDept, o.OHR_BizDomain
GO
Best Answer
It boils down to what
UPDATE
statement does. It's not entirely obvious but your statement is equivalent to this one:Since there is no mention of the
dbo.tAccts
table in theFROM
and no join or where condition between the table and the view, it results in aCROSS
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:
or (using your version):
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: