SQL Executor syntax for update statement using @Value for a string

[MichaelKohler]

We have a FME workspace where I need to update rows in the table that meet a where condition. The condition is a string value. When I set up the sql statement it is like this.

UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where 'PARCELID' = @Value(PARCEL_NUMBER)

and when the executor is run, the below error is returned for each feature.

Query failed, possibly due to a malformed statement. Query Text   UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where 'PARCELID' = 342073103690008  Provider error `(-2147217913) Error converting data type varchar to numeric.'

The PARCEL_NUMBER and the PARCELID fields are TEXT in both instances. This is what was used in python.

UPDATE ADDRESSES_TEST SET internalonly = 1 WHERE PARCELID = '{}'

 where the PARCEL_NUMBER is quoted. Also, internalonly is a smallint in both databases

Any leads would be greatly appreciated.

[BruceHarold]

Michael the single quotes around PARCELID make it a literal and not an identifier.  Just remove them.

[MichaelKohler]

Thanks for the quick reply.

Both statements with, and without the single quote return the same error.

UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where 'PARCELID' = @Value(PARCEL_NUMBER)

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where 'PARCELID' = 340750300650005'. Provider error `(-2147217913) Error converting data type varchar to numeric.'

and

UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where PARCELID = @Value(PARCEL_NUMBER)

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where PARCELID = 342073108650005'. Provider error `(-2147217913) Error converting data type nvarchar to numeric.'

The parcel number is a text string and should have quotes. This works with python and in SQL.

update ADDRESSES_TEST set internalonly = 1 where PARCELID = '342073103690008'

if i take the quotes away from the PARCELID and run in SQL, the same conversion error is given

Error converting data type nvarchar to numeric.

Very much appreciate your help.

[MarceloMarques · Accepted solution]

use SQL Server function CAST or CONVERT 
https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-...

[MichaelKohler]

Thank you. That did the trick

UPDATE ADDRESSES.dbo.ADDRESSES_TEST set internalonly = 1 where PARCELID = CAST(@Value(PARCEL_NUMBER) As nvarchar)

[MarceloMarques]

Glad to help.