How to do this.. - 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)

How to do this..

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-22 : 02:14:06

Hello,

I Have a table like this
--------------------------
MatchId TeamID SegmentID
--------------------------
100 12 1
100 13 1
101 17 1
101 19 1
102 21 1
102 25 1
-------------------------

Now i need to update the SegmentId, so after updating it, my table content should be like this...
--------------------------
MatchId TeamID SegmentID
--------------------------
100 12 1
100 13 0
101 17 1
101 19 0
102 21 1
102 25 0
-------------------------
Can any one help me please..

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-05-22 : 02:33:41

what's the logic? Every other line is 0?, or is it the second matchID is 0??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 02:49:38

quote:
Originally posted by ganeshkumar08

Hello,

I Have a table like this
--------------------------
MatchId TeamID SegmentID
--------------------------
100 12 1
100 13 1
101 17 1
101 19 1
102 21 1
102 25 1
-------------------------

Now i need to update the SegmentId, so after updating it, my table content should be like this...
--------------------------
MatchId TeamID SegmentID
--------------------------
100 12 1
100 13 0
101 17 1
101 19 0
102 21 1
102 25 0
-------------------------
Can any one help me please..

Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

Update t
SET t.SegmentID=0
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY MatchId ORDER BY TeamId) AS RowNo,
* 
FROM YourTable)t
WHERE t.RowNo >1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-22 : 02:53:59

[code]DECLARE @Sample TABLE (MatchID INT, TeamID INT, SegmentID INT)

INSERT @Sample
SELECT 100, 12, 1 UNION ALL
SELECT 100, 13, 1 UNION ALL
SELECT 101, 17, 1 UNION ALL
SELECT 101, 19, 1 UNION ALL
SELECT 102, 21, 1 UNION ALL
SELECT 102, 25, 1

SELECT *
FROM @Sample

UPDATE s
SET s.SegmentID = CASE WHEN x.TeamID IS NULL THEN 0 ELSE 1 END
FROM @Sample AS s
LEFT JOIN (
SELECT MIN(TeamID) AS TeamID
FROM @Sample
GROUP BY MatchID
) AS x ON x.TeamID = s.TeamID

SELECT *
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-22 : 03:27:19

thank you very much..
I got the logic..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources