June 28, 2012 at 10:06 am
Does anything appear to be wrong with the following CASE Statement?
It always updates the First Column correctly but in most instances it does not update the 2nd and 3rd Columns.
UPDATE Hist
SET ABCField =
CASE
WHEN Field1 = '2012' THEN 'Y'
ELSE 'N'
END,
DEFField =
CASE
WHEN Field2 = '9999' THEN 'Y'
END,
XyZField =
CASE
WHEN Field2 = '9999' THEN 'Y'
END
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2012 at 10:17 am
Looks ok to me.
Please post DDL etc etc... 😀
June 28, 2012 at 10:21 am
Thanks. I would post the DDL but the DB is of a confidential nature so all that I can do is make something up.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2012 at 10:34 am
I don't know if you want this behavior or not, but since your last two cases don't have elses, it will set those fields to null if Field2 does not equal 9999.
June 28, 2012 at 10:37 am
roryp 96873 (6/28/2012)
I don't know if you want this behavior or not, but since your last two cases don't have elses, it will set those fields to null if Field2 does not equal 9999.
and on that note if those 2 columns are null all ready then you would see a null updated to null when field2 is not '9999'
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
June 28, 2012 at 10:37 am
Welsh Corgi (6/28/2012)
Does anything appear to be wrong with the following CASE Statement?It always updates the First Column correctly but in most instances it does not update the 2nd and 3rd Columns.
UPDATE Hist
SET ABCField =
CASE
WHEN Field1 = '2012' THEN 'Y'
ELSE 'N'
END,
DEFField =
CASE
WHEN Field2 = '9999' THEN 'Y'
END,
XyZField =
CASE
WHEN Field2 = '9999' THEN 'Y'
END
You don't have ELSE clause for 2nd & 3rd column.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 28, 2012 at 10:38 am
I just checked and none of the CASE Statements are updating all of the records.
Does anyone have an example of a Sercehed CASE Statement used to perform UPDATES.
One that works.:hehe:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2012 at 10:41 am
Welsh Corgi (6/28/2012)
I just checked and none of the CASE Statements are updating all of the records.Does anyone have an example of a Sercehed CASE Statement used to perform UPDATES.
One that works.:hehe:
When you run your update statement, do you get an error, or does it say (x rows(s) affected) after you've run it? It sounds like things are being updated, but they may not being changed the way you are expecting.
June 28, 2012 at 10:46 am
Welsh Corgi (6/28/2012)
I just checked and none of the CASE Statements are updating all of the records.Does anyone have an example of a Sercehed CASE Statement used to perform UPDATES.
One that works.:hehe:
Unless there is an error the update you posted will update ABCField for every row in the table. You said to make it either Y or N and there is no where clause.
Here is an example of an update with a CASE statement that will update every row in a table and it works. 😛
UPDATE Hist
SET ABCField =
CASE
WHEN Field1 = '2012' THEN 'Y'
ELSE 'N'
END
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 28, 2012 at 10:52 am
Welsh Corgi (6/28/2012)
I just checked and none of the CASE Statements are updating all of the records.Does anyone have an example of a Sercehed CASE Statement used to perform UPDATES.
One that works.:hehe:
Well, without a WHERE clause, all rows will be updated: To Y or N on FieldABC, and Y or NULL on DEF & XYZ fields:
declare @hist table (id int identity(1,1), field1 varchar(10), field2 varchar(10), ABCField varchar(10), DEFField varchar(10), XyZField varchar(10))
insert into @hist (field1, field2, ABCField, DEFField, XyZField) values
('2012', '9999', null, null, null),
('2012', '9999', '1', '1', '1'),
(null, null, '1', '1', '1'),
('2002', '1111', '1', '1', '1'),
(null, null, null, null, null)
select * from @hist
UPDATE @Hist
SET ABCField =
CASE
WHEN Field1 = '2012' THEN 'Y'
ELSE 'N'
END,
DEFField =
CASE
WHEN Field2 = '9999' THEN 'Y'
END,
XyZField =
CASE
WHEN Field2 = '9999' THEN 'Y'
END
select * from @hist
June 28, 2012 at 12:13 pm
I have almost 200 Columns that I have to update,
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2012 at 12:37 pm
looks to me like you'll need 200 update statements, one for each possibility: otherwise you end up with unintended updates.
UPDATE SOMETABLE
SET ABCField =
CASE
WHEN Field1 = '2012' THEN 'Y'
ELSE 'N'
END
WHERE Field1 = '2012'
Lowell
June 28, 2012 at 1:10 pm
Since you can't post the actual table DDL and sample (made up data) for the table that demonstrates your problem, not much we can do unless you can put together a similiar issue using a table data that at least mirrors your problem. Not know exactly what is happening makes it difficult for us to create a situation that mirrors yours.
June 28, 2012 at 2:06 pm
Lowell (6/28/2012)
looks to me like you'll need 200 update statements, one for each possibility: otherwise you end up with unintended updates.
I tried that and I encountered something very strange.
I perform an Update to just update the record to 'Y' if it matched the criteria specified in the WHERE Clause but it updates not Just the record that are specified in the WHERE.
Then I perform a simple UPDATE WHERE <> and it updates the records with a 'Y' to a 'N'.
Very strange. I have never seen anything like it.
I appreciate the feedback from everyone:-).
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
June 28, 2012 at 2:09 pm
All we are doing is taking shots in the dark. We can't see what you see. You have to set something up that at least mirrors the problem you are having and give that to us.
We don't have the time to try and figure out what you are doing in an attempt to figure out a solution. Please help us help you.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply