tsql - 从选择语句更新表 [英] tsql - updating a table from a select statement

问题描述

我有两张桌子.表A和表B.
TableA 包含一个 varbinary(max) 列 - 名为 [BinaryA]TableB 包含一个列(名为Volume"，类型为Long")，其中包含每个 varbinary 卷.

I have two tables. TableA and TableB.
TableA holds a varbinary(max) column - named [BinaryA] TableB holds a column (named "Volume", type "Long") that contains each varbinary volume.

为了选择我查询的所有卷

in order to select all volumes I query

SELECT ID, MyVolume = DATALENGTH([Binary])
FROM [VAULT].[TABLEA]

比，我想用它的体积更新 tableB.

than, I want to update tableB with its volume.

然后我写

update [TableB] 
set [VOLUME]  = ( SELECT MyVolume = DATALENGTH([Binary])
              FROM [VAULT].[TABLEA] ab
          WHERE id = ab.[Id])

我收到比

Cannot insert the value NULL into column 'Volume', table 'MySchema.Asset';
column does not allow nulls. UPDATE fails.

虽然我在运行时没有收到任何 NULL

Though I dont receive any NULL when I run

SELECT ID, MyVolume = DATALENGTH([Binary])
FROM [VAULT].[TABLEA]

推荐答案

试试这个查询:

UPDATE TableB 
SET TableB.[VOLUME] = DATALENGTH([T2.Binary])
FROM TableB
INNER JOIN [VAULT].[TABLEA] T2 ON TableB.TAL_ID = T2.TAL_ID

假设 TableB 和 [VAULT].[TABLEA] 通过 ID 字段相关.

Assuming that TableB and [VAULT].[TABLEA] are related by ID field.

这篇关于tsql - 从选择语句更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！