CODEWITHSUNDEEP
sqlsql-server-2008cursor
kanan
kanan

Reputation: 41

SQL Update Using Cursor

I have Table Stucture like this 

GameID     Player1  Player2   Player3     Player4     Player5      conrti_P1   Contri_P2   Conrti_P3 Contri_P4   Contri_P5 

1             1        2         3          4           5              0           0         0           0         0

2             2        1         5          3           4              0           0        0            0        0

The columns Player1 through Player5 contains player ID's. The Contri_p1 through Contri_p5 columns contains their point values (initially 0 for all ). Now I want to UPDATE the points of a player. Let's say for example I want to update the points of player id = 4 in gameID = 1. The position of player id is not fixed every time. I need to search and update.

How can I do this?

Upvotes: 0

Views: 186

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

I would not try to come up with some clever (read convoluted) way trying to cram your updates into your current design but alter the design of your tables.

BEGIN TRAN

CREATE TABLE Games (GameID INT PRIMARY KEY)
CREATE TABLE Players (PlayerID INT PRIMARY KEY)
CREATE TABLE PlayersGames (GameID INT, PlayerID INT, Position INT, conrti INT)

INSERT INTO Games VALUES (1), (2)
INSERT INTO Players VALUES (1), (2), (3), (4), (5)
INSERT INTO PlayersGames VALUES (1,1,1,0), (1,2,2,0), (1,3,3,0), (1,4,4,0), (1,5,5,0), (2,2,1,0), (2,1,2,0), (2,5,3,0), (2,3,4,0), (2,4,5,0)

ROLLBACK TRAN

Now your UPDATE can be as simple as

UPDATE PlayersGames SET contri = @YourValue WHERE PlayerID = 4 AND GameID = 1

Edit
For completenes, the entire script, including showing the results as per your question might look like this

BEGIN TRAN

CREATE TABLE dbo.Games (GameID INT PRIMARY KEY)
CREATE TABLE dbo.Players (PlayerID INT PRIMARY KEY)
CREATE TABLE dbo.PlayersGames (GameID INT, PlayerID INT, Position INT, contri INT)
ALTER TABLE dbo.PlayersGames ADD CONSTRAINT FK_PLAYERSGAMES_PLAYERS FOREIGN KEY (PlayerID) REFERENCES dbo.Players(PlayerID)
ALTER TABLE dbo.PlayersGames ADD CONSTRAINT FK_PLAYERSGAMES_GAMES FOREIGN KEY (GameID) REFERENCES dbo.Games(GameID)

INSERT INTO Games VALUES (1), (2)
INSERT INTO Players VALUES (1), (2), (3), (4), (5)
INSERT INTO PlayersGames VALUES (1,1,1,0), (1,2,2,3), (1,3,3,0), (1,4,4,0), (1,5,5,0), (2,2,1,0), (2,1,2,0), (2,5,3,0), (2,3,4,0), (2,4,5,0)

SELECT  GameID
        , Player1 = MIN(CASE WHEN Position = 1 THEN PlayerID ELSE NULL END)
        , Player2 = MIN(CASE WHEN Position = 2 THEN PlayerID ELSE NULL END)
        , Player3 = MIN(CASE WHEN Position = 3 THEN PlayerID ELSE NULL END)
        , Player4 = MIN(CASE WHEN Position = 4 THEN PlayerID ELSE NULL END)
        , Player5 = MIN(CASE WHEN Position = 5 THEN PlayerID ELSE NULL END)
        , contri_P1 = MIN(CASE WHEN Position = 1 THEN contri ELSE NULL END)
        , contri_P2 = MIN(CASE WHEN Position = 2 THEN contri ELSE NULL END)
        , contri_P3 = MIN(CASE WHEN Position = 3 THEN contri ELSE NULL END)
        , contri_P4 = MIN(CASE WHEN Position = 4 THEN contri ELSE NULL END)
        , contri_P5 = MIN(CASE WHEN Position = 5 THEN contri ELSE NULL END)
FROM    PlayersGames
GROUP BY
        GameID

ROLLBACK TRAN

Upvotes: 3

Related Questions