我正在尝试运行此更新语句,但是,我收到一个错误,内容为“将 varchar 转换为数字时出错”。字段的数据类型如下:
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
)
查询如下
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
--请尝试如下-您的数据类型应该相同。我认为你应该先解决这个问题。如果您不能请尝试以下-
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))
--更好的解决方案将是-
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句