SQL code update table

Question

I have two tables:

Master Table Assets Table -AssetNo- -AssetNo- 

AssetNo is PK, and it is a foreign key for linking two tables. Now I would like to update using:

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

If I use this command, all assets with the same AssetNo will be automatically updated to 1.

How to encode a specific AssetNo IE: WHERE 111(from Assets)=111(from Master)

Answer 1

If I understand your question correctly, I think you just need a different condition in your WHERE clause:

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

Answer 2

What sql engine are you using? Something like this will work for 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