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

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-11-05 : 07:28:12

Hi

i have update query like this based on the number of records
i have one more column weight value

take the maximun order value in that table and passing the TOP value

for example

Update TOP (10) test_table
set column = 'A'
where column is null

bove top 10 update value is 'A' based on the maxmimum order value

so i will pass the TOP value

A=5
B=3
C=2

samples


weight               status
  
100	A
90	A
80	A
70	A
60	A
50	B
40	B
30	B
20	C
10	C

help me out

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-05 : 07:32:59

I'am not able to understand what you want - sorry.

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-05 : 08:03:51

I'll put my psychic powers to the test. Plus I need the posts to hit the 1000 mark!

declare @table table (col1 int, col2 char(1))
insert into @table(col1)
SELECT 100 UNION-- A
SELECT 90 UNION-- A
SELECT 80 UNION--UNION-- A
SELECT 70 UNION--A
SELECT 60 UNION-- A
SELECT 50 UNION-- B
SELECT 40 UNION-- B
SELECT 30 UNION-- B
SELECT 20 UNION-- C
SELECT 10 --C

UPDATE t
SET Col2 =
CASE
WHEN t1.Row between 1 and 5 THEN 'A'
WHEN t1.Row between 6 and 8 THEN 'B'
WHEN t1.Row between 9 and 10 THEN 'C'
END

FROM @table t
INNER JOIN
(
select col1,[Row] = Row_number() over(order by col1 desc )
from @table
) t1
ON
t.col1 = t1.col1

select * from @table

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources