SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to update required rows in a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 11/29/2012 :  08:11:42  Show Profile  Reply with Quote
i have table need to update required rows

i have 5 records for example i need to update only half records rest should remain same

in 100 records my condition id = 1 are 50 and condition id = 2 are should be 50
UserId DefinitionId ConditionId
1 1 2
2 1 2
3 1 2
4 1 2
5 2 2
6 2 2

my output should come like this one :

UserId DefinitionId ConditionId
1 1 1
2 1 1
3 1 1
4 1 2
5 2 2
6 2 2


P.V.P.MOhan

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 11/29/2012 :  08:31:43  Show Profile  Reply with Quote


CREATE TABLE tabTest(UserId int, DefinitionId int,ConditionId int)
insert into tabTest VALUES(1, 1, 2), (2, 1, 2 ), (3, 1, 2 ), (4, 1, 2 ), (5, 2, 2 ), (6, 2, 2 )

;with cte 
as ( 
	SELECT *, row_number() OVER(ORDER BY UserId) rn, COUNT(*) over() cnt 
	FROM tabTest
	)
UPDATE t
SET t.conditionId = CASE WHEN rn <= cnt/2 THEN 1 ELSE 2 END
FROM tabTest t JOIN cte c ON t.UserId = c.UserId

SELECT * FROM tabTest

Note: Here UserId is Unique 


--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 11/29/2012 :  08:53:31  Show Profile  Reply with Quote
hey chandu it is just a simple query but the way my question asked made to look complicated..

update table name set userid = 1 where userid = 2 and id between 1 and 100

but thanks i learned new thing using CTE

P.V.P.MOhan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 11/29/2012 :  09:04:46  Show Profile  Reply with Quote
How "update table name set userid = 1 where userid = 2 and id between 1 and 100" ?

means there is only exact 100 ids........

I am not getting your point.. Ok let it be..


--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 11/29/2012 :  09:23:06  Show Profile  Reply with Quote
No chandu it have 3 lakh records in which id = 1 have 80000 records in that one i need to change ID = 2 only 40000 records in that scenario my query worked

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/02/2012 :  02:23:10  Show Profile  Reply with Quote

quote:
Originally posted by mohan123

No chandu it have 3 lakh records in which id = 1 have 80000 records in that one i need to change ID = 2 only 40000 records in that scenario my query worked

P.V.P.MOhan


Thats straightforward so far as you've a unique valued column to identify those 40000 records

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000