How can I update a record using a correlated subquery?
- by froadie
I have a function that accepts one parameter and returns a table/resultset. I want to set a field in a table to the first result of that recordset, passing in one of the table's other fields as the parameter. If that's too complicated in words, the query looks something like this:
UPDATE myTable
SET myField = (SELECT TOP 1 myFunctionField
FROM fn_doSomething(myOtherField)
WHERE someCondition = 'something')
WHERE someOtherCondition = 'somethingElse'
In this example, myField and myOtherField are fields in myTable, and myFunctionField is a field return by fn_doSomething. This seems logical to me, but I'm getting the following strange error:
'myOtherField' is not a recognized OPTIMIZER LOCK HINTS option.
Any idea what I'm doing wrong, and how I can accomplish this?
*UPDATE: *
Based on Anil Soman's answer, I realized that the function is expecting a string parameter and the field being passed is an integer. I'm not sure if this should be a problem as an explicit call to the function using an integer value works - e.g. fn_doSomething(12345) seems to automatically cast the number to an string. However, I tried to do an explicit cast:
UPDATE myTable
SET myField = (SELECT TOP 1 myFunctionField
FROM fn_doSomething(CAST(myOtherField AS varchar(1000)))
WHERE someCondition = 'something')
WHERE someOtherCondition = 'somethingElse'
Now I'm getting the following error:
Line 5: Incorrect syntax near '('.
