Avatar of Larry Brister
Larry Brister
Flag for United States of America

asked on 

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

Microsoft SQL ServerRemote Access

Avatar of undefined
Last Comment
Mark Wills

8/22/2022 - Mon