How to update in this scenario??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to update in this scenario???

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-11-29 : 04:38:20

Hello all

i have table like users

UserId DefinitionId ConditionId UserName
1 1 1 joseph
2 1 1 khan
3 1 1 gary
4 1 1 brown
5 2 2 Jim
6 2 2 jay

my output should come like this one :

UserId DefinitionId ConditionId UserName
1 1 1 joseph
2 1 2 khan
3 1 3 gary
4 1 4 brown
5 2 1 Jim
6 2 2 jay

here condition id should get numbers like 1,2,3,4 etc for definition ID which as 1,1,1,1,2,2

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-29 : 04:49:31

[code]
DECLARE @tab TABLE(UserId int, DefinitionId int, ConditionId int, UserName varchar(10))
INSERT INTO @tab
SELECT 1, 1, 1, 'joseph' union all
SELECT 2, 1, 1, 'khan' union all
SELECT 3, 1, 1, 'gary' union all
SELECT 4, 1, 1, 'brown' union all
SELECT 5, 2, 2, 'Jim' union all
SELECT 6, 2, 2, 'jay'

;with cte AS (SELECT *, ROW_NUMBER() over(partition by definitionid order by userid) rn FROM @tab)
update t SET t.ConditionId = c.rn
FROM @tab t JOIN cte c ON t.UserId = c.UserId

SELECT * FROM @tab
[/code]

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-11-29 : 05:16:11

hey chandu asd in your scenario its correct but i gave my question wrong..conditionid is all 1,1,1,1,1 not 1,1,1,1,2,2 see condition ID suggest me

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-29 : 05:31:24

This code is independent of ConditionIds


DECLARE @tab TABLE(UserId int, DefinitionId int, ConditionId int, UserName varchar(10))
INSERT INTO @tab
SELECT 1, 1, 1, 'joseph' union all
SELECT 2, 1, 1, 'khan' union all
SELECT 3, 1, 1, 'gary' union all
SELECT 4, 1, 1, 'brown' union all
SELECT 5, 2, 1, 'Jim' union all
SELECT 6, 2, 1, 'jay'

;with cte AS (SELECT *, ROW_NUMBER() over(partition by definitionid order by userid) rn FROM @tab)
update t SET t.ConditionId = c.rn
FROM @tab t JOIN cte c ON t.UserId = c.UserId

SELECT * FROM @tab

--OUTPUT
UserId	DefinitionId	ConditionId	UserName
1	1		1		joseph
2	1		2		khan
3	1		3		gary
4	1		4		brown
5	2		1		Jim
6	2		2		jay

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources