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

General SQL Server Forums

New to SQL Server Programming

update

Author

Topic

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-06-11 : 09:28:19

Hi
If i wanna update the top x amount of colums in a table ?
what is the syntax of this ? or better if i select the top x amount of data from a table, how can i perform an update on a column from that query only ?
example.

I want to update the top 200 cx_update from :

select cx_update from cx_TABLE ct, contact c
where ct.code = c.code and c.status = '0'

so i need something like :

UPDATE cx_TABLE set top 200 cx_update = 'xxx'
where ct.code = c.code and c.status = '0'

??

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-11 : 09:31:42

If it is SQL 2005, you can use new TOP operator support for UPDATE statement.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-06-11 : 09:36:20

nah its not SQL 2005, what about an update stmt akin to :

UPDATE TABLE set x = '....'
FROM (select top 200 from ....) ??

X002548
Not Just a Number

15586 Posts

Posted - 2007-06-11 : 09:37:57

First of all, I never understood this requirement?

Why do you want to do this?

I'd really like to know how this makes any sense

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

pazzy11
Posting Yak Master

145 Posts

Posted - 2007-06-11 : 09:39:53

basically instead of updating ALL of the results from the SELECT stmt, we only need to update the top X amount of them ..

sshelper
Posting Yak Master

216 Posts

Posted - 2007-06-11 : 09:44:36

You can use the SET ROWCOUNT to limit the number of records to be updated.

SET ROWCOUNT 200

UPDATE cx_TABLE set cx_update = 'xxx'
where ct.code = c.code and c.status = '0'

SET ROWCOUNT 0

SQL Server Helper
http://www.sql-server-helper.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-11 : 09:47:12

Something like this may be:

Update t1
set col = 'something'
From table t1 JOIN (Select top 200 * from table t2 order by somecol) t2 on t1.primary-key = t2.primary-key

But I still don't understand, why top n? why not using some WHERE condition to filter out unwanted records?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 09:50:59

If it is only for display, dont update in the table. Do it in select itself

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources