nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

pslgisAdmin

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.

Find more posts tagged with

Accepted answers

Marcelo Marques

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-ver16

All comments

pslgisAdmin

Thank you. That did the trick

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

Marcelo Marques

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-ver16

pslgisAdmin

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.

bharold

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.