0

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)

2
Contributors
2
Replies
8
Views
7 Years
Discussion Span
Last Post by afrofish
1
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;
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.