CODEWITHSUNDEEP
t-sqlsql-server-2008-r2updating
faiz
faiz

Reputation: 93

TSQL Update with Subquery

I'm created the below Update which is performing badly. I feel like there might be an easier or more efficient way to make the query rather than my brute force attempt. Please help.

Update LocalServers 
set MustIPL = 1 
where LocalServerID IN (SELECT distinct olu.LocationID 
                        from OfferLocUpdate olu with (NoLock) 
                        Join LocalServers ls WITH (NOLOCK) on olu.LocationID = ls.LocationID 
                        Join CPE_IncentiveDLBuffer DLB with (NoLock) on olu.OfferID = dlb.OfferID 
                        where 
                             olu.LastSent > IncentiveLastHeard 
                             and CreationDate < dateadd(d, -@Days, getdate()) 
                             and DLB.FileName LIKE '%IDL-A')

Upvotes: 1

Views: 237

Answers (1)

valiik
valiik

Reputation: 111

Try this: 

    DECLARE @DateNow DATETIME
    SET @DateNow = GETDATE()

    ;WITH cte_DLB (OfferID, [FileName]) AS
    (
        SELECT OfferID, [FileName]
        FROM CPE_IncentiveDLBuffer (NOLOCK)
        WHERE  DLB.[FileName] LIKE '%IDL-A'
    )
    UPDATE 
        LS
    SET 
        LS.MustIPL = 1
    FROM 
        LocalServers LS 
        JOIN OfferLocUpdate OLU (NOLOCK) ON LS.LocalServerID = OLU.LocationID
        JOIN LocalServers ls (NOLOCK) ON OLU.LocationID = LS.LocationID 
        JOIN cte_DLB ON OLU.OfferID = cte_DLB.OfferID
    WHERE 
        OLU.LastSent > IncentiveLastHeard 
        AND CreationDate < dateadd(d, -@Days, @DateNow)

It will be very helpful, if you clarified to which table belong columns 'IncentiveLastHeard' & 'CreationDate'

Upvotes: 2

Related Questions