UPDATE更新一些但不是所有行

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.

当我使用底部的SELECT运行以下代码(在代码中注释掉)时,我得到12行。然后我注释掉SELECT并取消注释UPDATE,然后运行它。然后返回SELECT,有四行。当我使用SELECT时,8已更新并且不再被选中,但这四个仍然存在。再次切换回UPDATE,当我选择时仍然有这四行。

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

2 个解决方案

#1

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.

如果更新语句找到与要更新的记录匹配的多条记录,则它将使用第一条匹配记录中的值进行更新。在您的语句中,WHERE rc.Route <> rd.Route将允许更新第二组记录(如果存在其他匹配项),因为第一个更新的集将被过滤掉。第三次传递将再次更新第一组记录,因为第二次更新解除了第一次更新。

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:

这是一个如何发生这种情况的简单示例。一遍又一遍地执行这个sql并使用相同的更新语句来回观察t1_value开关:

-- 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

#2

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.

CTE仅在其定义之后的一个查询的持续时间内有效。因此,您获得的内容与先前的运行无关,因为CTE不会持久存在。

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.

如果要更新数据,则更新要更改数据的表而不是cte。然后从CTE中选择以查看是否仍有适用的记录。

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

如果您想要使用CTE数据执行更多步骤,请改用临时表或表变量。

#1