SQL代码更新表

Question

我有两个表：

Master Table       Assets Table
-AssetNo-           -AssetNo-

AssetNo 是 PK，它是链接两个表的外键。现在，我想更新使用：

UPDATE Assets 
   SET status = 1
  FROM Assets, Master
 WHERE Assets.AssetNo = Master.AssetNo

如果我使用此命令，具有相同 assetNo 的所有资产将自动更新为 1。

如何使用特定 assetNo IE 进行编码： WHERE 111（来自资产）=111（来自主数据）

Answer 1

如果我正确理解你的问题，我认为你只需要在 WHERE 子句中添加另一个条件：

UPDATE Assets 
   SET status = 1
  FROM Assets, Master
 WHERE Assets.AssetNo = Master.AssetNo
   AND Assets.AssetNo = 111

Answer 2

你使用什么sql引擎？像这样的东西适用于 sql server：

Update a
SET Status = 1
FROM Assets a
JOIN Master m on a.AssetNo = m.AssetNo
WHERE a.AssetNo  = 111

Answer 3

UPDATE Assets SET status = 1
FROM Assets a JOIN Master m ON a.AssetNo = m.AssetNo
WHERE a.AssetNo = 999

Answer 4

UPDATE a
SET a.Status = 1
FROM Assets AS a
INNER JOIN Master AS m ON a.AssetNo = m.AssetNo
WHERE m.AssetNo = @value