Unable to update - 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)

Unable to update

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-09 : 07:03:14

Hi, i have one table which contains 250 million records. I added one column to that table and i want to add highest value(column name) to that column. I have query also. But i am unable to do this thing on my machine. Though my server is good configuration one..its keep on executing and restarting the server. How can i update my table.
the sample data and script is as below.


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

The problem is with update query :(

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-09 : 08:34:08

Is it fixed 3 columns (RED, BLUE, GREEN) ?

Is this a one time job or regular process ?

KH
[spoiler]Time is always against us[/spoiler]

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-10 : 01:53:33

The table contains 10 columns. Yes its one time job

quote:
Originally posted by khtan

Is it fixed 3 columns (RED, BLUE, GREEN) ?

Is this a one time job or regular process ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-10 : 03:38:45

perform the update in smaller chucks of data. use "set rowcount" to limit the number of affected rows within a loop


set rowcount 10000

< your update query here>

set rowcount 0

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 03:52:17

quote:
Originally posted by khtan

perform the update in smaller chucks of data. use "set rowcount" to limit the number of affected rows within a loop

set rowcount 10000

< your update query here>

set rowcount 0

KH
[spoiler]Time is always against us[/spoiler]

Beware of this
http://beyondrelational.com/blogs/madhivanan/archive/2009/03/04/beware-of-the-usage-of-set-rowcount.aspx

Madhivanan

Failing to plan is Planning to fail

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-10 : 04:28:15

Hi, how can i set this after executing one set.

i.e i will update first 10,000 records by giving set rowcount 10000
i agree, first 10,000 records were updated.

Next how can i set the rowcount for next 10,000 records
i.e it has to start from 10001 to 20000(update query need to run on this range).

How can i give this?

quote:
Originally posted by madhivanan

quote:
Originally posted by khtan

perform the update in smaller chucks of data. use "set rowcount" to limit the number of affected rows within a loop

set rowcount 10000

< your update query here>

set rowcount 0

KH
[spoiler]Time is always against us[/spoiler]

Beware of this
http://beyondrelational.com/blogs/madhivanan/archive/2009/03/04/beware-of-the-usage-of-set-rowcount.aspx

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-10 : 04:39:43

quote:
Originally posted by Mng

Hi, how can i set this after executing one set.

i.e i will update first 10,000 records by giving set rowcount 10000
i agree, first 10,000 records were updated.

Next how can i set the rowcount for next 10,000 records
i.e it has to start from 10001 to 20000(update query need to run on this range).

How can i give this?

quote:
Originally posted by madhivanan

quote:
Originally posted by khtan

perform the update in smaller chucks of data. use "set rowcount" to limit the number of affected rows within a loop

set rowcount 10000

< your update query here>

set rowcount 0

KH
[spoiler]Time is always against us[/spoiler]

Beware of this
http://beyondrelational.com/blogs/madhivanan/archive/2009/03/04/beware-of-the-usage-of-set-rowcount.aspx

Madhivanan

Failing to plan is Planning to fail

please Try ,Top Clause with Update Statement eg:
UPDATE TOP (10000)Tbl
set colmn=value

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 04:40:39

Try this


WHILE EXISTS(select * from #Colors WHERE HIGH_COLOR IS NULL)
BEGIN
	UPDATE top (10000) 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
	AND r.HIGH_COLOR IS NULL
END

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 04:42:34

haroon2k9, Did you delete your post asking about "How to use TOP in update?"?

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-10 : 04:46:18

quote:
Originally posted by madhivanan

haroon2k9, Did you delete your post asking about "How to use TOP in update?"?

Madhivanan

Failing to plan is Planning to fail

yes.i deleted it having said that i was curious to know about the Top clause after reading your link about that and then i googled and got to know about how to use it.So i did that..

kindly iam asking,is this mistake?

Mng
Yak Posting Veteran

59 Posts

Posted - 2010-03-10 : 05:00:31

Hi Madhivan, pls check about performance here.
As i said before my table contains 200 million records..how can use this statement simply
"WHILE EXISTS(select * from #Colors WHERE HIGH_COLOR IS NULL)"

And also the internal query also doing partion and unpivoting for all rows..how can i avoid this?

pls welcome the suggestions.

quote:

Originally posted by madhivanan

Try this


WHILE EXISTS(select * from #Colors WHERE HIGH_COLOR IS NULL)
BEGIN
	UPDATE top (10000) 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
	AND r.HIGH_COLOR IS NULL
END

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 05:29:45

<<
kindly iam asking,is this mistake?
>>

It is not a problem

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 05:32:49

quote:
Originally posted by Mng

Hi Madhivan, pls check about performance here.
As i said before my table contains 200 million records..how can use this statement simply
"WHILE EXISTS(select * from #Colors WHERE HIGH_COLOR IS NULL)"

And also the internal query also doing partion and unpivoting for all rows..how can i avoid this?

pls welcome the suggestions.

quote:
Originally posted by madhivanan

Try this

WHILE EXISTS(select * from #Colors WHERE HIGH_COLOR IS NULL)
BEGIN
UPDATE top (10000) 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
AND r.HIGH_COLOR IS NULL
END

Madhivanan

Failing to plan is Planning to fail

You can use

WHILE EXISTS(select * from #Colors WHERE HIGH_COLOR IS NULL)
BEGIN
	UPDATE top (10000) 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 WHERE AND HIGH_COLOR IS NULL
)t
	UNPIVOT(Value FOR Color IN ([RED],[BLUE],GREEN))u
	) p
	ON p.SID=r.SID
	AND p.Seq=1
END

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-10 : 05:42:27

quote:
Originally posted by madhivanan

<<
kindly iam asking,is this mistake?
>>

It is not a problem

Madhivanan

Failing to plan is Planning to fail

Okay.Thanks.I thought i made a mistake by seeing your question..
Thanks Again,
Haroon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources