MS SQL Server Better way to do update?

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

Larry Bristersr. DeveloperAsked: 2017-12-15

Who is Participating?

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium. Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 55

Vitor MontalvãoMSSQL Senior EngineerCommented: 2017-12-15

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial