SQL 根据小数数据类型更新数字列

发布于 2024-11-06 00:00:19 字数 568 浏览 1 评论 0原文

我正在尝试从十进制（10,2）更新数字（5,2）类型的列，但该列仍为空，并且没有错误消息。

DECLARE @newDurationAsDec decimal(10,2) 
DECLARE @newDurationAsNum numeric(5,2)

--@newDurationAsDec is set by some logic from another table and holds the correct value e.g 2.00

set @newDurationAsNum = CAST(@newDurationAsDec AS numeric(5,2)) 
--selecting @newDurationAsNum contains the correct value e.g. 2.00

UPDATE table
SET Duration = @newDurationAsNum
WHERE ID = @ID AND
      Duration IS NULL AND
      OtherColumn = 'T'

不会返回任何错误，并且不会更新该列。将更新更改为选择会返回正确的行。有人可以指出我的错误吗？提前致谢。

原文

I am trying to update a column of type numeric(5,2) from decimal(10,2) but the column is remaining null and I have no error messages.

DECLARE @newDurationAsDec decimal(10,2) 
DECLARE @newDurationAsNum numeric(5,2)

--@newDurationAsDec is set by some logic from another table and holds the correct value e.g 2.00

set @newDurationAsNum = CAST(@newDurationAsDec AS numeric(5,2)) 
--selecting @newDurationAsNum contains the correct value e.g. 2.00

UPDATE table
SET Duration = @newDurationAsNum
WHERE ID = @ID AND
      Duration IS NULL AND
      OtherColumn = 'T'

No errors are retruned and the column is not updated. Changing the update to a select returns the correct row. can someone point out my mistake?
Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

花辞树 2024-11-13 00:00:19

下面对我来说效果很好。检查是否存在可能干扰事物的触发器。

如果您至少使用 SQL Server 2005，则可以使用 OUTPUT 子句查看更新的行，如下所示。

CREATE TABLE #T
(
ID INT PRIMARY KEY,
Duration NUMERIC(5,2),
OtherColumn CHAR(1)
)

INSERT INTO #T VALUES (1,NULL,'T')

DECLARE @ID INT = 1
DECLARE @newDurationAsDec DECIMAL(10,2) = 2

DECLARE @newDurationAsNum NUMERIC(5,2)
SET @newDurationAsNum = CAST(@newDurationAsDec AS NUMERIC(5,2)) 
SELECT @newDurationAsNum

UPDATE #T
SET Duration = @newDurationAsNum
OUTPUT inserted.*, deleted.*
WHERE ID = @ID AND
  Duration IS NULL AND
  OtherColumn = 'T'

SELECT * FROM #T  

DROP TABLE #T

Works fine for me below. Check there are no triggers that might be interfering with things.

If you are on at least SQL Server 2005 you can use the OUTPUT clause to see the row(s) updated as illustrated below.

CREATE TABLE #T
(
ID INT PRIMARY KEY,
Duration NUMERIC(5,2),
OtherColumn CHAR(1)
)

INSERT INTO #T VALUES (1,NULL,'T')

DECLARE @ID INT = 1
DECLARE @newDurationAsDec DECIMAL(10,2) = 2

DECLARE @newDurationAsNum NUMERIC(5,2)
SET @newDurationAsNum = CAST(@newDurationAsDec AS NUMERIC(5,2)) 
SELECT @newDurationAsNum

UPDATE #T
SET Duration = @newDurationAsNum
OUTPUT inserted.*, deleted.*
WHERE ID = @ID AND
  Duration IS NULL AND
  OtherColumn = 'T'

SELECT * FROM #T  

DROP TABLE #T

回复收藏 0 原文

万劫不复 2024-11-13 00:00:19

运行选择以查看您将更新的内容。我嵌入了我的更新语句，如下所示，因此只需通过注释和取消注释代码行就可以轻松地从选择到更新来回移动

--UPDATE t
--SET Duration = @newDurationAsNum 
Select *,   @newDurationAsNum
FROM table t
WHERE ID = @ID AND
       Duration IS NULL AND
       OtherColumn = 'T'

Run a select to see what you will be updating. I embed my updates statements as shown below so it is easy to move back and forth from the select to the update just by commenting and uncommenting lines of code

--UPDATE t
--SET Duration = @newDurationAsNum 
Select *,   @newDurationAsNum
FROM table t
WHERE ID = @ID AND
       Duration IS NULL AND
       OtherColumn = 'T'

回复收藏 0 原文

~没有更多了~