August 2, 2019 at 2:54 pm
..
August 3, 2019 at 2:54 pm
USE TestDB;
GO
--Weird question, pleas try this in Results to Text mode:
CREATE TABLE dbo.my_table (my_field INT);
INSERT dbo.my_table VALUES (1),(2),(3),(30),(40),(50);
GO
-- Syntax error in CASE clause!
UPDATE dbo.my_table SET my_field = CASE WHEN 1 THEN 10 WHEN 2 THEN 20 END;
GO
/*
Results
(6 row(s) affected)
Msg 4145, Level 15, State 1, Line 10
An expression of non-boolean type specified in a context where a condition is expected,
near 'THEN'.
*/
--If you correct your CASE syntax error to:
UPDATE dbo.my_table SET my_field = CASE my_field WHEN 1 THEN 10 WHEN 2 THEN 20 END;
--then incorrectly used CASE will destroy your data with result set Null, 10, 20.
SELECT DISTINCT my_field FROM dbo.my_table;
GO
/*
(6 row(s) affected)
Results
my_field
-----------
NULL
10
20
(3 row(s) affected)
*/
--Reset my_table values:
TRUNCATE TABLE dbo.my_table;
INSERT dbo.my_table VALUES (1),(2),(3),(30),(40),(50);
GO
/*
Results
(6 row(s) affected)
*/
--The correct used clause CASE for your task is:
UPDATE dbo.my_table SET my_field = CASE my_field
WHEN 1 THEN 10
WHEN 2 THEN 20
ELSE my_field
END;
SELECT DISTINCT my_field FROM dbo.my_table;
GO
/*
Results
(6 row(s) affected)
(6 row(s) affected)
my_field
-----------
3
10
20
30
40
50
(6 row(s) affected)
*/
DROP TABLE dbo.my_table;
--If you used NOT NULL in the column definition my_field, then incorrectly used CASE
--could not destroy your data:
CREATE TABLE dbo.my_table (my_field INT NOT NULL);
INSERT dbo.my_table VALUES (1),(2),(3),(30),(40),(50);
UPDATE dbo.my_table SET my_field = CASE my_field WHEN 1 THEN 10 WHEN 2 THEN 20 END;
SELECT DISTINCT my_field FROM dbo.my_table;
GO
/*
Results
(6 row(s) affected)
Msg 515, Level 16, State 2, Line 67
Cannot insert the value NULL into column 'my_field', table 'TestDB.dbo.my_table';
column does not allow nulls. UPDATE fails.
The statement has been terminated.
my_field
-----------
1
2
3
30
40
50
(6 row(s) affected)
*/
DROP TABLE dbo.my_table;
GO
August 4, 2019 at 9:34 pm
To all, I apologize for the bad example in the CASE Statement and it was not a trick question. Yes, it should have been the following:
CASE my_field ...
This was my first post and I thought that SQL Server Central would review the question before posting. I did not even think it would get approved. I just found out it was published.
I ran across this scenario as I was trying to quickly update some data with unintentional outcome. Good thing I had a backup and found the issue right away after running the script. I thought this could be a reminder to all that a simple straight forward query can have bad consequences to your data which points to the importance of testing pre and post.
I hope that this helps someone in the future!
Owen
Owen White
August 4, 2019 at 9:35 pm
Thank you! That was my mistake. I thought the QoD would get reviewed before they published it.
Owen White
August 5, 2019 at 6:18 am
The Syntax looked wrong based on what I knew but then I don't know everything so thought I'm going to learn another Microsoft shortcut. I actually like the incorrect Syntax. Too bad.
August 5, 2019 at 7:06 am
I understand Owen's intent, but the BIG lesson here is that you should think twice when running an update statement without a where clause. 🙂
5ilverFox
Consulting DBA / Developer
South Africa
August 5, 2019 at 6:54 pm
To all, I apologize for the bad example in the CASE Statement and it was not a trick question. Yes, it should have been the following:
CASE my_field ...
This was my first post and I thought that SQL Server Central would review the question before posting. I did not even think it would get approved. I just found out it was published.
I ran across this scenario as I was trying to quickly update some data with unintentional outcome. Good thing I had a backup and found the issue right away after running the script. I thought this could be a reminder to all that a simple straight forward query can have bad consequences to your data which points to the importance of testing pre and post.
I hope that this helps someone in the future!
Owen
Thanks a lot for coming back and posting all of this. Good lesson about updates and much respect for coming back and explaining the problems with the question.
Sue
August 7, 2019 at 10:02 pm
Alternatively, put a WHERE clause on the statement so it won't have unnecessary IO of updating rows that don't need to change.
This is a really important point. If you are updating a number of columns, and want to execute only a single UPDATE statement (to minimize I/O), then using the ELSE to pass through the existing value is reasonable. But if you are only updating a single column, definitely use a WHERE clause to ensure you only update the record if the data is chaning. UPDATEs that don't change data still result in I/O (both for the tran log and the data page), cause triggers to fire (which can be useful in some circumstances), result in TRUE from UPDATE(column), etc.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply