CODEWITHSUNDEEP
sql-server
user2447136
user2447136

Reputation: 189

Error converting from varchar to numeric

I am trying to run this update statement, however, I getting an error that reads 'Error converting varchar to numric'. Data types of the fields are as follows:

dbo.keyona.customer_id_num is a decimal(16,0)
s.customer_identifier is varchar(250)
s.customer_cis is varchar(250)
k.dedupe_static is decimal(20,0

)

Query is as follows

update dbo.keyona
set dbo.keyona.customer_id_num = s.customer_identifier
from dbo.keyona k join [StorageDB - BAW].[dbo].[MI_NedLife_Mapper_Embedded_Linked_201804] s
on  s.customer_cis = k.dedupe_static

Upvotes: 0

Views: 85

Answers (4)

user2447136
user2447136

Reputation: 189

update dbo.keyona
set dbo.keyona.customer_id_num = CAST(s.customer_identifier as decimal(16,0))                               
from dbo.keyona k join [StorageDB - BAW].[dbo].[MI_NedLife_Mapper_Embedded_Linked_201804] s
on  CAST(s.customer_cis as VARCHAR(500)) = CAST(k.dedupe_static as VARCHAR(500))
WHERE TRY_PARSE(s.customer_identifier as decimal(16,0)) IS NOT NULL

Upvotes: 0

Pawan Kumar
Pawan Kumar

Reputation: 2011

--Please try like below- Your data types should be same. I think your should fix that first. If you cannot pls try below-

update dbo.keyona
set dbo.keyona.customer_id_num = CASE WHEN TRY_PARSE(s.customer_identifier as decimal(16,0)) IS NOT NULL THEN 
                                CAST(s.customer_identifier as decimal(16,0))
                                ELSE 
                                    dbo.keyona.customer_id_num
                                END
from dbo.keyona k join [StorageDB - BAW].[dbo].[MI_NedLife_Mapper_Embedded_Linked_201804] s
on  CAST(s.customer_cis as VARCHAR(500)) = CAST(k.dedupe_static as VARCHAR(500))

--Much better solution will be-

update dbo.keyona
set dbo.keyona.customer_id_num = CAST(s.customer_identifier as decimal(16,0))                               
from dbo.keyona k join [StorageDB - BAW].[dbo].[MI_NedLife_Mapper_Embedded_Linked_201804] s
on  CAST(s.customer_cis as VARCHAR(500)) = CAST(k.dedupe_static as VARCHAR(500))
WHERE TRY_PARSE(s.customer_identifier as decimal(16,0)) IS NOT NULL

Upvotes: 1

Kevin T
Kevin T

Reputation: 132

Convert(decimal(16,0), s.customer_identifier)

in both places you're referencing customer_identifier

This is assuming you don't have alpha characters in that column.

Upvotes: -1

EzLo
EzLo

Reputation: 14189

Identify records that can't be correctly converted with:

SELECT
    s.customer_identifier
from 
    dbo.keyona k 
    join [StorageDB - BAW].[dbo].[MI_NedLife_Mapper_Embedded_Linked_201804] s on  s.customer_cis = k.dedupe_static
WHERE
    TRY_PARSE(s.customer_identifier AS decimal(16,0)) IS NULL

Then you will have to either filter them, or fix them so they can be converted correctly.

Upvotes: 2

Related Questions