我想在一个表中更新一个列，在其他表上进行连接，例如:
UPDATE table1 a
INNER JOIN table2 b ON a.commonfield = b.[common field]
SET a.CalculatedColumn= b.[Calculated Column]
WHERE
b.[common field]= a.commonfield
AND a.BatchNO = '110'
但它在抱怨:
警报170,15层，状态1,2号线 第2行:'a'附近的语法错误。
这里出了什么问题?
当前回答
试着这样做:
UPDATE a
SET a.CalculatedColumn= b.[Calculated Column]
FROM table1 a INNER JOIN table2 b ON a.commonfield = b.[common field]
WHERE a.BatchNO = '110'
其他回答
我也有同样的问题。而且你不需要添加一个物理列。因为现在你必须维护它。 你能做的就是在select查询中添加一个泛型列:
EX:
select tb1.col1, tb1.col2, tb1.col3 ,
(
select 'Match' from table2 as tbl2
where tbl1.col1 = tbl2.col1 and tab1.col2 = tbl2.col2
)
from myTable as tbl1
Try:
UPDATE table1
SET CalculatedColumn = ( SELECT [Calculated Column]
FROM table2
WHERE table1.commonfield = [common field])
WHERE BatchNO = '110'
使用MYSQL的用户
UPDATE table1 INNER JOIN table2 ON table2.id = table1.id SET table1.status = 0 WHERE table1.column = 20
试着这样做:
UPDATE a
SET a.CalculatedColumn= b.[Calculated Column]
FROM table1 a INNER JOIN table2 b ON a.commonfield = b.[common field]
WHERE a.BatchNO = '110'
我想，这就是你要找的。
UPDATE
Table1
SET
Table1.columeName =T1.columeName * T2.columeName
FROM
Table1 T1
INNER JOIN Table2 T2
ON T1.columeName = T2.columeName;
