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.
Author |
Topic |
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2012-11-29 : 08:11:42
|
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 50UserId 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 ConditionId1 1 1 2 1 1 3 1 1 4 1 2 5 2 2 6 2 2 P.V.P.MOhan |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-29 : 08:31:43
|
[code]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 tSET t.conditionId = CASE WHEN rn <= cnt/2 THEN 1 ELSE 2 ENDFROM tabTest t JOIN cte c ON t.UserId = c.UserIdSELECT * FROM tabTestNote: Here UserId is Unique [/code]--Chandu |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2012-11-29 : 08:53:31
|
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 100but thanks i learned new thing using CTEP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-11-29 : 09:04:46
|
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 |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2012-11-29 : 09:23:06
|
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 workedP.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-02 : 02:23:10
|
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 workedP.V.P.MOhan
Thats straightforward so far as you've a unique valued column to identify those 40000 records------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|