CODEWITHSUNDEEP
sqlsql-server
Eggybread
Eggybread

Reputation: 359

Copy data from 2 columns in to 1 column in another table MS SQL

I am trying to copy winners of a match stored in tblFixtures in to a single column in tblEntrants. So I basically want a winners list created in column AccountID in tblEntrants like this...

tblFixtures Columns: Player1 resultPLayer1 Player2 ResultPlayer2, CompID
                       john        5         stu         2        (Guid) 
                       dave        0         max         5        (Guid)

tblEntrants Columns: AccountID, CompID
                        john    (Guid)
                        dave    (Guid)

Here's what I have tried so far but it doesn't work..

INSERT INTO tblEntrants(AccountID,compID) SELECT(CASE WHEN (SELECT resultplayer1 FROM tblfixtures) > (SELECT resultplayer2 FROM tblfixtures) THEN (SELECT player1 FROM tblfixtures) END), @compID

INSERT INTO tblEntrants(AccountID,compID) SELECT(CASE WHEN (SELECT resultplayer2 FROM tblfixtures) > (SELECT resultplayer2 FROM tblfixtures) THEN (SELECT player2 FROM tblfixtures) END), @compID

Upvotes: 0

Views: 41

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS: you can do it using single SELECT statement with CASE

INSERT INTO tblEntrants(AccountID,compID)
SELECT
    CASE WHEN resultPLayer1 > ResultPlayer2 THEN player1 ELSE player2 END, @compID
FROM tblFixtures

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13403

You can use this.

INSERT INTO tblEntrants(AccountID,compID) 
SELECT CASE WHEN ResultPlayer2 > resultPLayer1 THEN Player2 ELSE Player1 END, CompID
FROM tblfixtures

Upvotes: 0

Related Questions