SQL 从临时表更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15118834/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update from Temp Table
提问by SF Developer
Query:
询问：
SELECT ID, T.c.value('@Address', 'nvarchar(20)' ) as Address
INTO #TMP
FROM TABLE1
CROSS APPLY XMLData.nodes('/Document') AS T(c)
UPDATE TABLE1
SET HomeAddress = (SELECT TOP 1 t.Address
FROM #TMP t
WHERE t.ID = ID)
Mainly, I need to copy data OUT from an XML field to normal fields within the same table.
主要是，我需要将数据从 XML 字段复制到同一个表中的普通字段。
Questions:
问题：
- Any reason why all the records get the HomeAddress on Table1?
- Is really Cursor the only way to update the value on Table1?
- 为什么所有记录都在 Table1 上获得 HomeAddress 的任何原因？
- Cursor 真的是更新 Table1 上的值的唯一方法吗？
回答by HLGEM
UPDATE T2
SET HomeAddress = t1.address
FROM TABLE2 t2
join TABLE1 t1 on T1.ID = t2.HomeAddressID
and t2.HomeAddress <> t1.address
Use a join. No need to temp table or correlated subquery.
使用连接。无需临时表或相关子查询。
If table 1 is in a one to many relationshisp these are some posibilites for handling that. If you havea value that indicates one and only one record (we have a field in our system that picks the most important address, it is maintained with a trigger to guarantee uniquesness), the try this:
如果表 1 处于一对多关系中，则这些是处理该问题的一些可能性。如果您有一个值指示一个且只有一个记录（我们的系统中有一个字段可以选择最重要的地址，它由触发器维护以保证唯一性），请尝试以下操作：
UPDATE T2
SET HomeAddress = t1.address
FROM TABLE2 t2
join TABLE1 t1 on t1.ID = t2.HomeAddressID
WHERE t1.somefield = 'somevalue'
and t2.HomeAddress <> t1.address
If you need to based the unique record on asome other field (such as the most recent date), then try a variation of this:
如果您需要将唯一记录基于某个其他字段（例如最近的日期），请尝试以下变体：
UPDATE T2
SET HomeAddress = t1.address
FROM TABLE2 t2
join TABLE1 t1 on t1.ID = t2.HomeAddressID
join (select id, max(somedatefield) from table1 group by id) t3 on t3.id = t1.id
Where t2.HomeAddress <> t1.address
回答by SF Developer
On the update, I need to FULLY QUALIFY the Table as follow:??????????????????
在更新时，我需要完全限定表格如下：?????????????????????
UPDATE TABLE1 SET TABLE1.HomeAddress = (SELECT TOP 1 t.Address
FROM #TMP t
WHERE t.ID = TABLE1.ID)
回答by Alejandro Bit Chakoch
SELECT P.TipoComprob,P.NoComprob,C.Importe as ImpIVA1,ROUND(100/P.ImpGravado*C.Importe,1)
as PorcIVA1
INTO #Temporal
FROM ComprobProv AS P
LEFT JOIN PasesCompras AS C ON C.TipoComprob=P.TipoAsiento AND
C.NoComprob=P.NoComprob
WHERE P.PorcIVA1 =0 and P.CatIVA = 'Ri' AND P.ImpGravado>0 AND C.CodCuenta=110303010 AND ROUND(100/P.ImpGravado*C.Importe,1) IN (21.00,10.50,27.00);
go
select * from #Temporal;
go
UPDATE
t1
SET
t1.ImpIVA1 = t2.ImpIVA1, t1.PorcIVA1 = t2.PorcIVA1
FROM
dbo.ComprobProv t1
INNER JOIN #Temporal t2
ON t1.TipoComprob = t2.TipoComprob AND t1.NoComprob = t2.NoComprob;
go
-- Note that the 'GO' are important to preserve the context in SQL Server 2017, otherwise you'll find an 'unknown field name' error.
-- 请注意，“GO”对于保留 SQL Server 2017 中的上下文很重要，否则您会发现“未知字段名称”错误。
