CODEWITHSUNDEEP
sqlsql-servercasting
5n0u7
5n0u7

Reputation: 199

SQL update numeric column from decimal datatype

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.

Upvotes: 1

Views: 4539

Answers (2)

Martin Smith
Martin Smith

Reputation: 453910

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

Upvotes: 3

HLGEM
HLGEM

Reputation: 96650

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'

Upvotes: 2

Related Questions