CODEWITHSUNDEEP
t-sqlsql-server-2012
ErocM
ErocM

Reputation: 4662

not sure how to populate a table based on the contents of another table

I've tried to figure my way around this but I'm relatively new to tsql.

These are my two tables:

This is my dbo.UsersAccountLink table:

enter image description here

This is my Company.Token tables:

enter image description here

Right now the UsersAccountLink.CorporationId is blank and I need to populate it based on what is in the Company.Token table.

So, I need to loop through each record in the Company.Token table and get the Company.Token.TokenId value and then query the Company.Token table with the TokenId, then lastly, I need to update the record on the dbo.UsersAccountLink table with the CorporationId.

Ultimately I want to update the dbo.UsersAccountLink.CorporationId with the value from Company.Token.CorporationId.

I hope that makes sense.

Well, here is what I have so far... It's not much but I'm struggling.

USE SuburbanPortal
go

-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(*) FROM dbo.UsersAccountLink) 

-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1

-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN

   -- Declare variables to hold the data which we get after looping each record

        DECLARE @CorpId UNIQUEIDENTIFIER, @TokenId UNIQUEIDENTIFIER

        -- Get the data from table and set to variables
        SET @TokenId = (SELECT [TokenId] FROM [SuburbanPortal].[dbo].[UsersAccountLink])
        SET @CorpId = (SELECT [CorporationId] FROM [SuburbanPortal].[Company].[Token] WHERE @TokenId = ???)


-- Increment the iterator
SET @I = @I  + 1

END

Upvotes: 0

Views: 34

Answers (1)

Code Different
Code Different

Reputation: 93161

Welcome to SQL Server. Your code indicates that you are coming from a programming background with this pattern called "row-by-agonizing-row" (ROAR). The first order of business is to replace the "loop" thinking with "join". Instead of looping through a table then search for match in the other, use join:

UPDATE UAL
    SET         UAL.CorporationId = T.CorporationId
    FROM        dbo.UserAccountLink UAL
    INNER JOIN  Company.Token       T   ON UAL.TokenId = T.TokenId

Upvotes: 1

Related Questions