MS SQL Server Better way to do update?

Larry Brister
Larry Brister used Ask the Experts™
on
My code below works... but it seems like I should be able to get by (Better???) without the separate update on the # temp table.

Would that be better?

IF OBJECT_ID('tempdb..#appointments_res') IS NOT NULL
    DROP TABLE #appointments_res;

    ;WITH cte
    AS ( SELECT a.IndividualID ,
                a.AppointmentID ,
                a.AppointmentDate ,
                a.AppointmentStartTime ,
                a.AppointmentTitle ,
                t.ReservationID ,
                CONVERT(VARCHAR(10), t.InDate, 126) CheckinDate ,
                ROW_NUMBER() OVER ( PARTITION BY a.IndividualID ,
                                                    a.AppointmentTitle
                                    ORDER BY a.IndividualID ,
                                                AppointmentDate DESC ) rn 
            FROM   ewAppointments a
                JOIN Individuals i ON a.IndividualID = i.IndividualID
                JOIN dbo.ewSalesSitesToIndividuals s ON i.IndividualID = s.IndividualID
                JOIN dbo.everyware_t_reservation t ON t.ReservationID = REVERSE(LEFT(REVERSE(a.AppointmentTitle), CHARINDEX(' ', REVERSE(a.AppointmentTitle)) - 1))
            WHERE  a.AppointmentDate > GETDATE() - 1
                AND ( a.AppointmentTitle LIKE 'Reservation' + '%' )
                AND i.Dupe = 0
                AND s.Active = 1
                AND s.SalesSiteID IN ( 103405, 103648 ))
SELECT *
INTO   #appointments_res
FROM   cte
WHERE  cte.AppointmentDate <> cte.CheckinDate;



UPDATE a
SET    a.AppointmentDate = aa.CheckinDate
FROM   ewAppointments a
        JOIN #appointments_res aa ON a.AppointmentID = aa.AppointmentID
WHERE  1 = 1;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
MSSQL Senior Engineer
Distinguished Expert 2017
Commented:
You don't need the temporary table:
;WITH cte
AS ( SELECT a.IndividualID ,
            a.AppointmentID ,
            a.AppointmentDate ,
            a.AppointmentStartTime ,
            a.AppointmentTitle ,
            t.ReservationID ,
            CONVERT(VARCHAR(10), t.InDate, 126) CheckinDate ,
            ROW_NUMBER() OVER ( PARTITION BY a.IndividualID ,
                                             a.AppointmentTitle
                                ORDER BY a.IndividualID ,
                                         AppointmentDate DESC ) rn 
        FROM   ewAppointments a
            JOIN Individuals i ON a.IndividualID = i.IndividualID
            JOIN dbo.ewSalesSitesToIndividuals s ON i.IndividualID = s.IndividualID
            JOIN dbo.everyware_t_reservation t ON t.ReservationID = REVERSE(LEFT(REVERSE(a.AppointmentTitle), CHARINDEX(' ', REVERSE(a.AppointmentTitle)) - 1))
        WHERE  a.AppointmentDate > GETDATE() - 1
            AND ( a.AppointmentTitle LIKE 'Reservation' + '%' )
            AND i.Dupe = 0
            AND s.Active = 1
            AND s.SalesSiteID IN ( 103405, 103648 ))
UPDATE a
SET    a.AppointmentDate = aa.CheckinDate
FROM   ewAppointments a
    JOIN (SELECT *
		FROM   cte
		WHERE  cte.AppointmentDate <> cte.CheckinDate
		) aa ON a.AppointmentID = aa.AppointmentID

Open in new window

Larry Bristersr. Developer

Author

Commented:
Perfect. Thanks
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Gosh that was very quick... well done :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial