Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

MS SQL Server Better way to do update?

Avatar of Larry Brister
Larry BristerFlag for United States of America asked on
Microsoft SQL ServerRemote Access
3 Comments1 Solution132 ViewsLast Modified:
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;
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor MontalvãoFlag of Switzerland imageIT Engineer
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answers