CODEWITHSUNDEEP
sqlsql-server
KWallace
KWallace

Reputation: 1700

UPDATE updates some, but not all rows

When I run the following code with the SELECT at the bottom (Commented out in the code), I get 12 rows. Then I comment out the SELECT and uncomment the UPDATE, and run it. Then go back to SELECT, and there are four rows. 8 got updated and are no longer selected when I use SELECT, but these four persist. Switch back to UPDATE again, and there are still these four rows when I SELECT.

Any ideas?

WITH cte AS (
    SELECT      r.Request_ID,
                rc.Route_id, 
                rc.Flight_Information_Region, 
                rc.Route AS rcRoute, 
                rd.FIR,
                rd.Route AS rdRoute
    FROM        Route_Country            rc
    INNER JOIN  Flight_Legs_Route_Header rh     ON (rc.Route_ID = rh.Route_ID)
    INNER JOIN  Flight_Legs_Route_DETAIL rd     ON (rh.Flight_Leg_Route_ID = rd.Flight_Leg_Route_ID AND rc.Flight_Information_Region = rd.FIR)
    INNER JOIN  Request                  r      ON (rh.Request_ID = r.Request_ID)
    WHERE       rc.Route <> rd.Route
    AND         r.Request_Archived = 'N'
)

UPDATE cte SET rdRoute = rcRoute
--SELECT * FROM cte

Upvotes: 0

Views: 86

Answers (2)

Ron Smith
Ron Smith

Reputation: 3266

If an update statement finds more than one record matching the record to be updated, it will update with the value from the first matched record. In your statement, the WHERE rc.Route <> rd.Route will allow a second set of records to be updated, if other matches exist, since the first updated set is filtered out. A third pass would again update the first set of records since the second update undid the first.

Here is a quick example of how this could happen. Execute this sql over and over again and watch the t1_value switch back and forth with the same update statement:

-- load test data
if object_id('tempdb..#test1') is null
    begin
        create table #test1 (id int identity,value int)
        insert into #test1 (value) values(1)
    end

if object_id('tempdb..#test2') is null
    begin
        create table #test2 (id int identity,test1_id int,value int)
        insert into #test2 (test1_id,value) values(1,1),(1,2)
    end

-- update with cte
;with cte as
    (
    select
        t1.id,
        t1.value as t1_value,
        t2.value as t2_value
    from #test1 as t1
        inner join #test2 as t2
            on t2.test1_id = t1.id
            and t2.value <> t1.value
    )
update cte set t1_value = t2_value

-- return update cte values
;with cte as
    (
    select
        t1.id,
        t1.value as t1_value,
        t2.value as t2_value
    from #test1 as t1
        inner join #test2 as t2
            on t2.test1_id = t1.id
            and t2.value <> t1.value
    )
select * from cte

Upvotes: 1

HLGEM
HLGEM

Reputation: 96552

CTEs are only good for the duration of the one query immediately following thier definition. So what you get is not related in any way to previous runs because the CTE is not persisted.

If you want to update the data, the update the table you want to change the data in not the cte. and then do the select from the CTE to see if any records are still applicable.

If you wnat to do more steps with the CTE data, then use a temp table or table variable instead.

Upvotes: 0

Related Questions