How to apply code to each row in a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to apply code to each row in a table

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-13 : 14:36:56

How to apply code to each row in a table

I havea table containing 5 columns.

ColumnCount.......1ML.......2ML.......3ML.......4ML
....0..............1.........3.........6.........2

I nees to evaluate the four ML columns and place the result in the ColumnCount column. The following code indicates how I need to evaluate each row in the table. What I don’t know is how to have this code applied to each row in the table? How do I embed this in a SELECT?

DECLARE @MatchLevel int, @MatchCount int

@MatchLevel = 3

IF 1ML <= @MatchLevel
BEGIN
@MatchCount = @MatchCount + 1
END
IF 2ML <= @MatchLevel
BEGIN
@MatchCount = @MatchCount + 1
END
IF 3ML <= @MatchLevel
BEGIN
@MatchCount = @MatchCount + 1
END
IF 4ML <= @MatchLevel
BEGIN
@MatchCount = @MatchCount + 1
END

ColumnCount = @MatchCount
@MatchCount = 0

The desired result for the example row would be:

ColumnCount.......1ML.......2ML.......3ML.......4ML
....3..............1.........3.........6.........2

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-13 : 16:39:50

Here is one way:


DECLARE @MatchLevel int
set @MatchLevel = 3

update myTable set
	columncount = 
	 case when [1ML] <= @matchLevel then 1 else 0 end + 
	 case when [2ML] <= @matchLevel then 1 else 0 end + 
	 case when [3ML] <= @matchLevel then 1 else 0 end + 
	 case when [4ML] <= @matchLevel then 1 else 0 end

Be One with the Optimizer
TG

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-13 : 17:07:49

Perfect! I knew there had to be an easy way. :)

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources