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)

Update

Author

Topic

NeilC
Yak Posting Veteran

55 Posts

Posted - 2008-07-12 : 16:36:04

I am trying to update a bit column of the top 2 records from a query

select col1, top 2 count(col2) as col2 from table1
group by col1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-12 : 16:48:21

Could you post some sample data to make your problem more clear?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

NeilC
Yak Posting Veteran

55 Posts

Posted - 2008-07-12 : 17:15:29

[code]
col1 col2 This needs updating to true
----------- ----------- ---------------------------
1 5
3 3
[/code]

Does this make it a little clearer? The records that get returned from the query need a column (bit) updating to true

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-12 : 22:37:22

It makes the output clearer, however how do you determine what two rows to return? Could you show us say 5-10 rows of data and then the expected output of that data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-13 : 02:34:41

It seems like this is what you're after

UPDATE t
SET t.BitField=1
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY Col2 DESC) AS RowNo,
Col1,
Col2
FROM YourTable)t
WHERE t.RowNo<=2

If this is not what you require please make your requirement more clear by means of some data.

NeilC
Yak Posting Veteran

55 Posts

Posted - 2008-07-13 : 11:37:53

I need to do this, but ALL the records are getting updated, nit the records in the sub-query


update	table1
set		bitCol = 1
where	exists		
						(
						 SELECT TOP 2 col1
						 FROM table2 
						 GROUP BY col1
						 ORDER BY COUNT(table2.col2) DESC
						 )

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-13 : 11:54:53

[code]
update table1
set bitCol = 1
where somecol in
(
SELECT TOP 2 col1
FROM table2
GROUP BY col1
ORDER BY COUNT(table2.col2) DESC
)
[/code]

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

NeilC
Yak Posting Veteran

55 Posts

Posted - 2008-07-13 : 12:07:05

Thanks! I tried that earlier an it failed, but I have col1, col2 in the select statement - oops

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-13 : 13:25:03

Try this too...

UPDATE t
SET t.BitField=1
FROM YourTable t
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY COUNT(Col2) DESC) AS RowNo,
Col1,
COUNT(Col2)
FROM YourTable)t1
ON t.Col1=t1.Col1
AND t1.RowNo<=2

NeilC
Yak Posting Veteran

55 Posts

Posted - 2008-07-14 : 08:56:03

I have never used over and partition before, is there any benefit than the above query?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources