mysql - 相同列类型更新错误

Question

我有一个表，我想更新其中一列值:

UPDATE t1 
SET [Value] = t2.[Value]
FROM table1 AS t1
INNER JOIN table2 AS t2
ON (t1.ID = t2.ID)

table1(表)和table2( View )结构相同。

当我运行上面的以下更新时，我得到:

Msg 8115, Level 16, State 7, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

我通过运行以下查询检查了列值是否不同:

SELECT 
c.name 'Column Name',
t.Name 'Data type',
c.max_length 'Max Length',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
sys.columns c
INNER JOIN 
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID('table1') --and the same for table2

获得相同的数据类型、最大长度等，并获得相同的结果。

那么为什么我会收到此错误？

我查过NUMERIC_ROUNDABORT但不太喜欢它......还有其他解决方案吗？

Answer 1

在 t2.Value 周围添加精度为 10 的 ROUND 函数会改变什么吗？实际精度取决于列定义。

UPDATE t1 
SET [Value] = ROUND(t2.[Value], 10)
FROM table1 AS t1
INNER JOIN table2 AS t2
ON (t1.ID = t2.ID

想查看 CREATE TABLE 语句。