SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t1 (id1 int PRIMARY KEY, x int);
CREATE TABLE t2 (id2 int PRIMARY KEY, id1 int, x int);
SET STATISTICS XML ON;
UPDATE x
SET id1 += 1
FROM (
    SELECT t1.*
    FROM t1
    JOIN t2 ON t2.id1 = t1.id1
) x;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="UPDATE x&#xa;SET id1 += 1&#xa;FROM (&#xa;    SELECT t1.*&#xa;    FROM t1&#xa;    JOIN t2 ON t2.id1 = t1.id1&#xa;) x" StatementId="1" StatementCompId="1" StatementType="UPDATE" StatementSqlHandle="0x0900E04647E2C64877A5DAAB80FD924122DC0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0313201" StatementEstRows="1.5" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xA8C4D5D2F2EA6ACF" QueryPlanHash="0x04992998DF8CB25A" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="15" CompileCPU="0" CompileMemory="320"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="814528" GrantedMemory="1024" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2638680"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="1" PhysicalOp="Clustered Index Update" LogicalOp="Update" EstimateRows="1.5" EstimateIO="0.0133784" EstimateCPU="1.5e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0313201" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="1"><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__DC501A3F09784BC3]" IndexKind="Clustered" Storage="RowStore"></Object><SetPredicate SetPredicateType="Insert"><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t1].[id1] = RaiseIfNullUpdate([fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t1].[id1]),[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t1].[x] = [fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t1].[x]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullUpdate"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><SetPredicate SetPredicateType="Update"><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t1].[x] = [fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t1].[x]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><ActionColumn><ColumnReference Column="Act1008"></ColumnReference></ActionColumn><RelOp NodeId="2" PhysicalOp="Collapse" LogicalOp="Collapse" EstimateRows="1.5" EstimateIO="0" EstimateCPU="3.5e-06" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0179402" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference><ColumnReference Column="Act1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Collapse><GroupBy><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></GroupBy><RelOp NodeId="3" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="2" EstimateIO="0.0112613" EstimateCPU="0.000103139" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0179367" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference><ColumnReference Column="Act1008"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Act1008"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="4" PhysicalOp="Split" LogicalOp="Split" EstimateRows="2" EstimateIO="0" EstimateCPU="1.5e-06" AvgRowSize="19" EstimatedTotalSubtreeCost="0.00657228" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference><ColumnReference Column="Act1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Split><DefinedValues><DefinedValue><ColumnReference Column="Act1008"></ColumnReference></DefinedValue></DefinedValues><ActionColumn><ColumnReference Column="Act1008"></ColumnReference></ActionColumn><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="19" EstimatedTotalSubtreeCost="0.00657078" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t1].[id1]+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00657068" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t1].[id1]=[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t2].[id1]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="7" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__DC501A3F09784BC3]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="8" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__DC501A3E720C4122]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Split></RelOp></Sort></RelOp></Collapse></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


DELETE x
FROM (
    SELECT t1.*
    FROM t1
    JOIN t2 ON t2.id1 = t1.id1
) x;
Msg 4405 Level 16 State 1 Line 1
View or function 'x' is not updatable because the modification affects multiple base tables.

WITH Joined AS (
    SELECT t1.x, t2.x x2
    FROM t1
    JOIN t2 ON t2.id1 = t1.id1
)
MERGE Joined
USING (VALUES(0)) v(dummy) ON 1=1
WHEN MATCHED THEN
  UPDATE SET
    x = 1;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH Joined AS (&#xa;    SELECT t1.x, t2.x x2&#xa;    FROM t1&#xa;    JOIN t2 ON t2.id1 = t1.id1&#xa;)&#xa;MERGE Joined&#xa;USING (VALUES(0)) v(dummy) ON 1=1&#xa;WHEN MATCHED THEN&#xa;  UPDATE SET&#xa;    x = 1;" StatementId="1" StatementCompId="1" StatementType="MERGE" StatementSqlHandle="0x090023DEB663C03EF532AE4830058380D6FF0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0235335" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x9D05C2EB1027E317" QueryPlanHash="0xD7A5833E4EEFBEB9" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="432"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="814528" GrantedMemory="1024" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2638680"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="1" PhysicalOp="Clustered Index Merge" LogicalOp="Merge" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0235335" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__DC501A3F09784BC3]" IndexKind="Clustered" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t1].[x] = [Expr1007]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="x"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><ActionColumn><ColumnReference Column="Action1006"></ColumnReference></ActionColumn><RelOp NodeId="2" PhysicalOp="Assert" LogicalOp="Assert" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-07" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0235335" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Column="Action1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Assert StartupExpression="0"><RelOp NodeId="3" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="8e-08" AvgRowSize="27" EstimatedTotalSubtreeCost="0.023533" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Column="Action1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1012"></ColumnReference><ScalarOperator ScalarString="conditional_row_number"><Sequence FunctionName="conditional_row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="1" EstimateIO="0" EstimateCPU="2e-08" AvgRowSize="27" EstimatedTotalSubtreeCost="0.0235329" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Column="Action1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Segment1014"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1014"></ColumnReference></SegmentColumn><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0235329" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Column="Action1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0235328" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Column="Action1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar ComputeSequence="1"><DefinedValues><DefinedValue><ColumnReference Column="Action1006"></ColumnReference><ScalarOperator ScalarString="ForceOrder((1))"><Intrinsic FunctionName="ForceOrder"><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0056022" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0235327" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t1].[id1]=[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t2].[id1]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="8" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__DC501A3F09784BC3]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="9" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100011" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0146444" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="10" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__DC501A3E720C4122]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Sort></RelOp></Merge></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></Segment></RelOp></SequenceProject></RelOp><Predicate><ScalarOperator ScalarString="CASE WHEN [Expr1012]&gt;(1) THEN (0) ELSE NULL END"><IF><Condition><ScalarOperator><Compare CompareOp="GT"><ScalarOperator><Identifier><ColumnReference Column="Expr1012"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Predicate></Assert></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


WITH Joined AS (
    SELECT t1.x, t2.x x2
    FROM t1
    JOIN t2 ON t2.id1 = t1.id1
)
MERGE Joined
USING (VALUES(0)) v(dummy) ON 1=1
WHEN MATCHED THEN
  UPDATE SET
    x2 = 1;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH Joined AS (&#xa;    SELECT t1.x, t2.x x2&#xa;    FROM t1&#xa;    JOIN t2 ON t2.id1 = t1.id1&#xa;)&#xa;MERGE Joined&#xa;USING (VALUES(0)) v(dummy) ON 1=1&#xa;WHEN MATCHED THEN&#xa;  UPDATE SET&#xa;    x2 = 1;" StatementId="1" StatementCompId="1" StatementType="MERGE" StatementSqlHandle="0x090084C318E820DCA62A24C237D0C9C387EA0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.00657058" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xFDD9A8EA0EEDEFFE" QueryPlanHash="0x54B334FEDF8F826F" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="344"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2638680"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="1" PhysicalOp="Clustered Index Merge" LogicalOp="Merge" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00657058" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__DC501A3E720C4122]" IndexKind="Clustered" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t2].[x] = [Expr1007]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="x"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><ActionColumn><ColumnReference Column="Action1006"></ColumnReference></ActionColumn><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="19" EstimatedTotalSubtreeCost="0.00657058" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id2"></ColumnReference><ColumnReference Column="Action1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00657048" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id2"></ColumnReference><ColumnReference Column="Action1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar ComputeSequence="1"><DefinedValues><DefinedValue><ColumnReference Column="Action1006"></ColumnReference><ScalarOperator ScalarString="ForceOrder((1))"><Intrinsic FunctionName="ForceOrder"><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id2"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OuterReferences><RelOp NodeId="5" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__DC501A3E720C4122]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="6" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__DC501A3F09784BC3]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t2].[id1]"><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


WITH Joined AS (
    SELECT t1.x, t2.x x2
    FROM t1
    JOIN t2 ON t2.id1 = t1.id1
)
MERGE Joined
USING (VALUES(0)) v(dummy) ON 1=1
WHEN MATCHED THEN
  UPDATE SET
    x = 1,
    x2 = 1;
Msg 4405 Level 16 State 1 Line 1
View or function 'Joined' is not updatable because the modification affects multiple base tables.

WITH Joined AS (
    SELECT t1.*
    FROM t1
    JOIN t2 ON t2.id1 = t1.id1
)
MERGE Joined
USING (VALUES(0)) v(dummy) ON 1=0
WHEN NOT MATCHED BY SOURCE THEN DELETE;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH Joined AS (&#xa;    SELECT t1.*&#xa;    FROM t1&#xa;    JOIN t2 ON t2.id1 = t1.id1&#xa;)&#xa;MERGE Joined&#xa;USING (VALUES(0)) v(dummy) ON 1=0&#xa;WHEN NOT MATCHED BY SOURCE THEN DELETE;" StatementId="1" StatementCompId="1" StatementType="MERGE" StatementSqlHandle="0x09009BD5F83455491A9463B0C3ECEEDF373B0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0279333" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x10873C3D91541AFB" QueryPlanHash="0x7F317299A32B359E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="352"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="814528" GrantedMemory="1024" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2638680"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0279333" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__DC501A3F09784BC3]" IndexKind="Clustered" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-07" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0179323" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="2" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="8e-08" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0179319" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Column="Expr1011"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1011"></ColumnReference><ScalarOperator ScalarString="conditional_row_number"><Sequence FunctionName="conditional_row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="1" EstimateIO="0" EstimateCPU="2e-08" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0179318" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Column="Action1007"></ColumnReference><ColumnReference Column="Segment1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1012"></ColumnReference></SegmentColumn><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0179318" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Column="Action1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar ComputeSequence="1"><DefinedValues><DefinedValue><ColumnReference Column="Action1007"></ColumnReference><ScalarOperator ScalarString="ForceOrder((3))"><Intrinsic FunctionName="ForceOrder"><ScalarOperator><Const ConstValue="(3)"></Const></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100011" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0179317" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OuterReferences><RelOp NodeId="7" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__DC501A3E720C4122]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__DC501A3F09784BC3]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t2].[id1]"><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Sort></RelOp></ComputeScalar></RelOp></Segment></RelOp></SequenceProject></RelOp><Predicate><ScalarOperator ScalarString="[Expr1011]&lt;=(1)"><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Column="Expr1011"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


WITH Joined AS (
    SELECT t2.*
    FROM t1
    JOIN t2 ON t2.id1 = t1.id1
)
MERGE Joined
USING (VALUES(0)) v(dummy) ON 1=0
WHEN NOT MATCHED BY SOURCE THEN DELETE;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH Joined AS (&#xa;    SELECT t2.*&#xa;    FROM t1&#xa;    JOIN t2 ON t2.id1 = t1.id1&#xa;)&#xa;MERGE Joined&#xa;USING (VALUES(0)) v(dummy) ON 1=0&#xa;WHEN NOT MATCHED BY SOURCE THEN DELETE;" StatementId="1" StatementCompId="1" StatementType="MERGE" StatementSqlHandle="0x0900281CAC0B805719741858C7D7335DA3CF0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0279333" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x10873C3D91541AFB" QueryPlanHash="0x7F317299A32B359E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="360"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="814528" GrantedMemory="1024" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2638680"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0279333" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__DC501A3F09784BC3]" IndexKind="Clustered" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-07" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0179323" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="2" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="8e-08" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0179319" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Column="Expr1011"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1011"></ColumnReference><ScalarOperator ScalarString="conditional_row_number"><Sequence FunctionName="conditional_row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="1" EstimateIO="0" EstimateCPU="2e-08" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0179318" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Column="Action1007"></ColumnReference><ColumnReference Column="Segment1012"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1012"></ColumnReference></SegmentColumn><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0179318" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference><ColumnReference Column="Action1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar ComputeSequence="1"><DefinedValues><DefinedValue><ColumnReference Column="Action1007"></ColumnReference><ScalarOperator ScalarString="ForceOrder((3))"><Intrinsic FunctionName="ForceOrder"><ScalarOperator><Const ConstValue="(3)"></Const></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100011" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0179317" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OuterReferences><RelOp NodeId="7" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__DC501A3E720C4122]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__DC501A3F09784BC3]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t2].[id1]"><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Sort></RelOp></ComputeScalar></RelOp></Segment></RelOp></SequenceProject></RelOp><Predicate><ScalarOperator ScalarString="[Expr1011]&lt;=(1)"><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Column="Expr1011"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


WITH Joined AS (
    SELECT t1.*
    FROM t2
    JOIN t1 ON t2.id1 = t1.id1
)
MERGE Joined
USING (VALUES(0)) v(dummy) ON 1=0
WHEN NOT MATCHED BY SOURCE THEN DELETE;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH Joined AS (&#xa;    SELECT t1.*&#xa;    FROM t2&#xa;    JOIN t1 ON t2.id1 = t1.id1&#xa;)&#xa;MERGE Joined&#xa;USING (VALUES(0)) v(dummy) ON 1=0&#xa;WHEN NOT MATCHED BY SOURCE THEN DELETE;" StatementId="1" StatementCompId="1" StatementType="MERGE" StatementSqlHandle="0x09002CA434444D9190ADFF29138F5357E3A30000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0165714" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x7699125B49D0F700" QueryPlanHash="0x0E00F64201DE2B1E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="15" CompileCPU="15" CompileMemory="272"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2638680"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0165714" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__DC501A3E720C4122]" IndexKind="Clustered" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id2"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__DC501A3E720C4122]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="3" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__DC501A3F09784BC3]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t2].[id1]"><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


WITH Joined AS (
    SELECT t2.*
    FROM t2
    JOIN t1 ON t2.id1 = t1.id1
)
MERGE Joined
USING (VALUES(0)) v(dummy) ON 1=0
WHEN NOT MATCHED BY SOURCE THEN DELETE;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH Joined AS (&#xa;    SELECT t2.*&#xa;    FROM t2&#xa;    JOIN t1 ON t2.id1 = t1.id1&#xa;)&#xa;MERGE Joined&#xa;USING (VALUES(0)) v(dummy) ON 1=0&#xa;WHEN NOT MATCHED BY SOURCE THEN DELETE;" StatementId="1" StatementCompId="1" StatementType="MERGE" StatementSqlHandle="0x0900EE53EC1BA1A5E2A5827FCC0FFCD12D410000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0165714" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x7699125B49D0F700" QueryPlanHash="0x0E00F64201DE2B1E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="280"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2638680"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0165714" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__DC501A3E720C4122]" IndexKind="Clustered" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id2"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Index="[PK__t2__DC501A3E720C4122]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="3" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Index="[PK__t1__DC501A3F09784BC3]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t1]" Column="id1"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_45a0bc236eb54666bebeaec30db54e7c].[dbo].[t2].[id1]"><Identifier><ColumnReference Database="[fiddle_45a0bc236eb54666bebeaec30db54e7c]" Schema="[dbo]" Table="[t2]" Column="id1"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>