为什么从自联接临时表更新真实表不起作用，但在使用真实表时它起作用? [英] why does updating a real table from a self-join temp table is not working but when using a real table it works?

问题描述

场景如下:

过程 1 创建临时表 #t.

Procedure 1 creates temp table #t.

过程 1 执行过程 2.

Procedure 1 executes procedure 2.

过程 2 填充 #t.

Procedure 2 populates #t.

在过程 1 中，我从 #t 插入到一个真实的表中，以便我可以查看数据.数据就在那里.

In procedure 1, I insert into a real table from #t just so I can view the data. The data is there.

在查看此数据后，我立即使用自联接进行更新.像这样:

Immediately after viewing this data, I do an update with a self-join. Like so:

  update b
  set b.column1 = a.column3
  from #t a
    inner join #t b on a.id = b.id;

应该更新的记录没有更新.

The record that is supposed to be updated IS NOT UPDATING.

但是，如果我将 #t 更改为真正的表dbo.t"并执行完全相同的操作，它会起作用.

However, if I change #t to a real table "dbo.t" and do exactly the same thing, it works.

我很困惑.任何人都知道为什么会这样?谢谢.

I'm so confused. Anyone has any idea why this could be? Thanks.

推荐答案

根据 MS SQL 文档:

Per the MS SQL Docs:

在存储过程中创建的本地临时表被删除存储过程结束时自动执行.表可以是由存储执行的任何嵌套存储过程引用创建表的过程.该表不能被引用调用创建表的存储过程的进程.

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017

这篇关于为什么从自联接临时表更新真实表不起作用，但在使用真实表时它起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！