Hi All,

I am trying to convert the following access update query into TSQL without any luck:

UPDATE tblCattle INNER JOIN tblFarmDetails
ON tblCattle.strEartag = tblFarmDetails.EarTagNo
SET curMartPrice = tblFarmDetails.MartPric
WHERE (curMartPrice=0 Or curMartPrice Is Null);

First I tried simply pasting in the query with no luck so below is my revised (but unfruitful) attempt:

UPDATE farmlink.dbo.tblCattle
SET curMartPrice = FarmData.dbo.tblFarmDetails.MartPric
FROM FarmData.dbo.tblFarmDetails
INNER JOIN farmlink.dbo.tblCattle ON strEartag =FarmData.dbo.tblFarmDetails.EarTagNo
WHERE FarmData.dbo.tblFarmDetails.curMartPrice=0
Or FarmData.dbo.tblFarmDetails.curMartPrice Is Null;

If anyone could help steer me in the right direction I would be very grateful.

Regards John (Afro)

UPDATE farmlink.dbo.tblCattle
SET curMartPrice = FarmData.dbo.tblFarmDetails.MartPric
FROM FarmData.dbo.tblFarmDetails
INNER JOIN farmlink.dbo.tblCattle ON strEartag =FarmData.dbo.tblFarmDetails.EarTagNo
WHERE FarmData.dbo.tblFarmDetails.curMartPrice=0 
Or FarmData.dbo.tblFarmDetails.curMartPrice Is Null;

I assume that you are getting some kind of error. Please post.

Your where clause can be rewritten as follows in case the nulls are giving you fits:

WHERE isnull(FarmData.dbo.tblFarmDetails.curMartPrice,0)=0;

I tried your suggestion and it worked. Thanks Glent