11

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
  • show us the CREATE VIEW statement. – ypercubeᵀᴹ Dec 9 '15 at 12:26
  • And is OHR_EmpStatus a column of the table, the view or both? – ypercubeᵀᴹ Dec 9 '15 at 12:47
  • 1
    I am simply ASTONISHED at the amount of quick & excellent response to my question. As I said, it's probably something obvious that I've overlooked or misunderstood but I don't think it's because I've ended up with a cross-join. I should have posted the definitions for the tAccts table and vReclaimable view. I'll review all the answers posted so far and, if I still think they are not right on target, I'll add those definitions to the question. Meanwhile, a HUGE thanks to everyone who has responded. – ArleyD Dec 9 '15 at 16:24
  • Adding CREATE statements for both tAccts and vReclaimable, as requested by ypercube – ArleyD Dec 9 '15 at 18:41
18

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' ;
2

it seems that you have no join between the tables in your update query.

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'

here there must be something to match rows between the tables such as where tAccts.id = vReclaimable.id

2

Another way of putting this:

The issue is your belief that the statement "should update only the rows in the tAccts table which are returned by the vReclaimable view".

That's not the case. It updates all rows from tAccts (the table mentioned right after UPDATE) which match OHR_EmpStatus <> 'A' (the condition in the WHERE). It may use data from vReclaimable in doing so (but you make no reference to it whatsoever).

If you want to restrict it to rows that are in vReclaimable, in addition to the other options presented, you could use a subquery:

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)
WHERE OHR_EmpStatus <> 'A' AND tAccts.key IN (SELECT key FROM vReclaimable)
0

If the below query returns more than one row:

select 'tAccts table', dnis, method, destination, phone_tf, phone_local 
from tAccts
where OHR_EmpStatus <> 'A'

then you are trying to update multiple rows with the same values hence violating the unique constraint.

0

another option is : you dont need the

FROM dbo.vReclaimable

because you do not use any value from this table in your update statement.

  • For the record: in this case, the join to vReclaimable was doubtlessly intended to filter the table being updated. Though it's not needed for the SET clause, it's effectively part of the WHERE clause. – Jon of All Trades Jun 2 '17 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.