I am trying to update a field in one table based on the value of a a field in another table divided by another field in that table.
I am getting the following error:
Msg 235, Level 16, State 0, Line 10
Cannot convert a char value to money. The char value has incorrect syntax.
The error highlights the first line in the code below. Both [Total] fields are money and [Exchange Rate] is real. I am confused.
I am getting the following error:
Msg 235, Level 16, State 0, Line 10
Cannot convert a char value to money. The char value has incorrect syntax.
The error highlights the first line in the code below. Both [Total] fields are money and [Exchange Rate] is real. I am confused.
Code:
UPDATE [tblPurchaseOrderCustomFields]
SET [tblPurchaseOrderCustomFields].Total = p.[Total]/p.[Exchange Rate]
FROM [tblPurchaseOrderCustomFields] c
INNER JOIN [tblPurchaseOrdersCurrent] p
ON c.PurchaseOrderRef = p.[PO Number]
WHERE c.Total NOT IN
(
SELECT [PurchaseOrderRef]
FROM [tblPurchaseOrderCustomFields]
GROUP BY [PurchaseOrderRef]
HAVING COUNT(*) > 1
)