CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Tim
Tim

Reputation: 1249

Update each row in a table based on a value from that row

I'm trying to update each row in a Declared table with a value that is for each row. I don't want to write a cursor to do it I would rather use a set based operation but I am drawing a blank. Can anyone point me in the right direction?

The results I currently get for the ValidContactPhoneCount column is 82013 which is a count of all the valid numbers for all accounts in the table instead of for each account. I would expect 0 - 5 numbers for each row.

DECLARE @Open TABLE
    (
    AccountID INT,
    AccountProcessID INT,
    ServicerID INT,
    ProcessTemplateID INT,
    ProcessStageID INT,
    CurrentStage VARCHAR(50),
    CurrentStageID INT,
    ValidContactPhoneCount INT
    )

INSERT INTO @Open

-- Identify accounts in open inventory for servicers with campaigns by template and stage

SELECT  ap.AccountID,
        ap.AccountProcessID,
        ap.ServicerID,
        ap.ProcessTemplateID,
        ap.ProcessStageID,
        ps.StageDesc,
        ap.ProcessStageID,
        NULL
FROM AccountProcess ap WITH (NOLOCK)
    JOIN ProcessStage ps WITH (NOLOCK)
        ON ps.ProcessStageID = ap.ProcessStageID
    JOIN ADSDialerAutomation.Campaign dc WITH (NOLOCK)
        ON dc.ServicerID = ap.ServicerID
            AND dc.ProcessTemplateID = ap.ProcessTemplateID
            AND dc.ProcessStageID = ap.ProcessStageID
WHERE ProcessStatusID = 1

-- update @open table if eligible for Skip trace

UPDATE  @Open
SET     ValidContactPhoneCount = ( SELECT   COUNT(cp.ContactPhoneID)
                                   FROM     @Open o
                                            INNER JOIN dbo.AccountContact ac ON ac.AccountID = o.AccountID 
                                            INNER JOIN dbo.Contact c ON c.ContactID = ac.ContactID
                                            INNER JOIN dbo.ContactPhone cp ON cp.ContactID = c.ContactID
                                   WHERE    ac.AccountID = o.AccountID
                                            AND cp.PhoneTypeID NOT IN ( 5, 8 )
                                 )
FROM    @Open o
        INNER JOIN dbo.AccountContact ac ON ac.AccountID = o.AccountID 
WHERE   ac.AccountID = o.AccountID


SELECT * FROM @Open

Upvotes: 0

Views: 77

Answers (1)

Mr Moose
Mr Moose

Reputation: 6354

I haven't tested this...but I suspect the issue is in your derived table. I'd be more inclined to do something like;

UPDATE  @Open
SET     ValidContactPhoneCount = CountData.ValidCount
FROM    @Open o
        INNER JOIN dbo.AccountContact ac ON ac.AccountID = o.AccountID 
        INNER JOIN ( SELECT   COUNT(cp.ContactPhoneID) as ValidCount, oAccountID
                                   FROM     @Open o
                                            INNER JOIN dbo.AccountContact ac ON ac.AccountID = o.AccountID 
                                            INNER JOIN dbo.Contact c ON c.ContactID = ac.ContactID
                                            INNER JOIN dbo.ContactPhone cp ON cp.ContactID = c.ContactID
                                   WHERE    ac.AccountID = o.AccountID
                                            AND cp.PhoneTypeID NOT IN ( 5, 8 )
                                            GROUP BY o.AccountID
                                 ) CountData ON CountData.AccountID = o.AccountID
WHERE   ac.AccountID = o.AccountID

This way you are grouping your counts by account id, and then able to reference them by account. Like I say, I haven't tested this, but your lack of grouping in your derived table is why you are getting a total count.

Upvotes: 1

Related Questions