SQLTeam.com | Weblogs | Forums

How to update a column from a different DB?


Code is something like this:
Update Inventory
SET Price = (SELECT Inventory.Price
FROM farwest.dbo.Inventory T2
WHERE T2.ItemNum = Inventory.ItemNum);

I need to update the Price column in the Inventory table from DB farwest into the Price column from table Inventory in the sanroque DB.

I keep getting this error:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Price', table 'sanroque.dbo.Inventory'; column does not allow nulls. UPDATE fails.
The statement has been terminated.

I have no NULLS in either table!


You have an ItemNum in the Inventory table that is not found in the farwest.dbo.Inventory table.


Try this

Update T1
SET Price = T2.Price
FROM Inventory T1 inner join farwest.dbo.Inventory T2
On T2.ItemNum = T1 .ItemNum


I recommend using a SYNONYM for any "remote" table (i.e. using 3-part of 4-part naming), rather than the {linked-Server.}Database direct.

Solves the problem of fixing all the code when the external database moves / is renamed / something else happens to it!