Member Avatar for afrofish

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
  • 260 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by afrofish
Member Avatar for glennt 
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;
Member Avatar for afrofish

I tried your suggestion and it worked. Thanks Glent

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.