Update the table - SQL Server Forums

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)

Update the table

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-02-13 : 23:48:16

Hi, i want to update the column from below table by taking the highest value column name.


SID RED BLUE GREEN HIGH_COLOR
1   12  15   5      
2   45  56   6      
3   67  5    7      
4   78  7    79     

Now i want to update the column "High_Color" by getting the highest value(column name of that value) from that row. Output looks as below

SID RED BLUE GREEN HIGH_COLOR
1   12  15   5      BLUE
2   45  56   6      BLUE 
3   67  5    7      RED
4   78  7    79     GREEN

Please can any one provide the update query for this requirement.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-14 : 00:41:43

UPDATE T
SET T.HIGHCOLOR = M.HCOLOR
from TABLE T
INNER JOIN
(
SELECT *,Case When RED > BLUE and RED > GREEN Then 'RED'
     When BLUE > RED and BLUE > GREEN Then BLUE
     ELSE 'GREEN' END as HColor
 from TABLE
)M ON M.SID = T.SID

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-02-14 : 00:52:41

Hi Sodeep,

thanks for ur query. But i can't use this query bcz i have 160 column in my table(i just gave only 3 in forum) and around 200 million rows. How can do this now?

quote:

Originally posted by sodeep

UPDATE T
SET T.HIGHCOLOR = M.HCOLOR
from TABLE T
INNER JOIN
(
SELECT *,Case When RED > BLUE and RED > GREEN Then 'RED'
     When BLUE > RED and BLUE > GREEN Then BLUE
     ELSE 'GREEN' END as HColor
 from TABLE
)M ON M.SID = T.SID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-14 : 03:47:42

[code]CREATE Table #Colors
(
SID int, RED int, BLUE int, GREEN int, HIGH_COLOR varchar(100)
)

INSERT INTO #Colors (SID,RED,BLUE,GREEN)
SELECT 1, 12, 15, 5 union all
SELECT 2, 45, 56, 6 union all
SELECT 3, 67, 5, 7 union all
SELECT 4, 78, 7, 79

SELECT * FROM #Colors
UPDATE r
SET r.HIGH_COLOR=p.Color
FROM #Colors r
INNER JOIN
(
SELECT ROW_NUMBER() OVER (PARTITION BY SID ORDER BY Value DESC) AS Seq, *
FROM (SELECT SID,RED,BLUE,GREEN FROM #Colors)t
UNPIVOT(Value FOR Color IN ([RED],[BLUE],GREEN))u
) p
ON p.SID=r.SID
AND p.Seq=1

SELECT * FROM #Colors
drop table #Colors

output
-----------------------------------------
before
SID RED BLUE GREEN HIGH_COLOR
1 12 15 5 NULL
2 45 56 6 NULL
3 67 5 7 NULL
4 78 7 79 NULL
after update

SID RED BLUE GREEN HIGH_COLOR
1 12 15 5 BLUE
2 45 56 6 BLUE
3 67 5 7 RED
4 78 7 79 GREEN

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-14 : 07:52:09

Keep it simple

SET NOCOUNT ON

CREATE Table #Colors
(
SID int, RED int, BLUE int, GREEN int, HIGH_COLOR varchar(100)
)

INSERT INTO #Colors (SID,RED,BLUE,GREEN)
SELECT 1,   12,  15,   5      union all
SELECT 2,   45,  56,   6        union all  
SELECT 3,   67,  5,    7       union all   
SELECT 4,   78,  7,    79     

SELECT * FROM #Colors


UPDATE t
SET HIGH_COLOR =  (SELECT d.k
		         FROM (SELECT red, 'RED' FROM #colors WHERE SID=t.SID 
				   UNION ALL 
				   SELECT blue, 'BLUE' FROM #colors WHERE SID=t.SID 
				   UNION ALL 
				   SELECT green, 'GREEN' FROM #colors WHERE SID=t.SID) d(i, k)
			  WHERE d.i >=ALL
				  (SELECT red FROM #colors WHERE SID=t.SID 
				   UNION ALL 
				   SELECT blue FROM #colors WHERE SID=t.SID 
				   UNION ALL 
				   SELECT green FROM #colors WHERE SID=t.SID))
FROM #colors t;



SELECT * FROM #Colors
drop table #Colors

/*
Befor
SID         RED         BLUE        GREEN       HIGH_COLOR
----------- ----------- ----------- ----------- -------------
1           12          15          5           NULL
2           45          56          6           NULL
3           67          5           7           NULL
4           78          7           79          NULL

After
SID         RED         BLUE        GREEN       HIGH_COLOR
----------- ----------- ----------- ----------- ------------
1           12          15          5           BLUE
2           45          56          6           BLUE
3           67          5           7           RED
4           78          7           79          GREEN
*/

Sachin.Nand

2937 Posts

Posted - 2010-02-14 : 11:46:52

Here is my method.Its a cocktail of methods posted by Visakh & ms65g


create table #Colors 
(
SID int, RED int, BLUE int, GREEN int, HIGH_COLOR varchar(100)
)

INSERT INTO #Colors (SID,RED,BLUE,GREEN)
SELECT 1,   12,  15,   5      union all
SELECT 2,   45,  56,   6        union all  
SELECT 3,   67,  5,    7       union all   
SELECT 4,   78,  7,    79     

SELECT * FROM #Colors


update c  set c.High_color=t2.High_color

		from #Colors c inner join 

		(
		select * from
		(
					select RED,BLUE,GREEN,sid,

					(

							  select top 1 value from 
							  
							  
							  (
										  select  red as value union all
										  select  blue as value union all
										  select  green as value

							  )as d order by value desc
							  
							  
					  ) as maxvalue from #Colors
		  )T
		  UNPIVOT
		   (value FOR High_color IN (RED, blue, green)
		      
		)as upt where maxvalue=value

)t2 on c.SID=t2.SID


select * from #Colors


drop table #Colors

PBUH

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-14 : 16:01:51

use external column aliasing for beter style.

quote:

Originally posted by Idera


					  
	 
(select  red as value union all			   
select  blue as value union all	           
select  green as value)as d(value) order by value desc

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-02-14 : 23:25:29

quote:
Originally posted by ms65g

use external column aliasing for beter style.

quote:
Originally posted by Idera

(select red ~~as value~~ union all
select blue ~~as value~~ union all
select green ~~as value~~)as d(value) order by value desc

PBUH

Thanks for this.I will keep this in mind next time

PBUH

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-15 : 00:25:06

You are welcome.

Read "Programming Style by Joe CELKO" if you want to write code in great form.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources