May 7, 2016 at 8:10 am
Hello,
I have a table which has 4 fields - the last one does not accept null values. Consider the following declaration and insertion.
Declare @Table_A TABLE(id int, frs int, sec int,result decimal not null)
INSERT INTO @Table_A VALUES (1,7, 3, 0),
(2,33,5,0),
(3, 2, null, 0),
(4,5,1,0)
Update @Table_A set result = fst / sec
This update returns the following error. Is there any way for SQL to return the ID value of the row that triggered the error? In this case, I would expect "3"
(4 row(s) affected)
Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column 'result', table '@Table_A'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
May 7, 2016 at 8:21 am
maybe
Update @Table_A set result = ISNULL(frs / sec,0)
select * from @table_A WHERE result = 0
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 7, 2016 at 8:36 am
might be useful to consider divide by zero as well
DECLARE @Table_A TABLE
(id INT,
frs INT,
sec INT,
result DECIMAL NOT NULL
);
INSERT INTO @Table_A VALUES
(1,7,3,0),
(2,33,5,0),
(3, 2, null, 0),
(4,5,1,0),
(10,5,0,0),
(10,5,NULL,0),
(10,NULL,0,0)
UPDATE @Table_A
SET result = ISNULL(frs / NULLIF(sec, 0), 0);
SELECT *
FROM @table_A
SELECT *
FROM @table_A
WHERE result = 0;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply