CODEWITHSUNDEEP
sqlsql-serverdatabase
Mortenkp25
Mortenkp25

Reputation: 181

Update a table's newly added column with matching records SQL Server

I've recently added a new foreign key constraint (ProjectID) to my Invoice table and I'm trying to update the Invoice table so that the foreign key matches with a given set of records.

Using the following query, I'm able to find the matching records. 

SELECT 
    IV.InvoiceID, TA.ProjectID 
FROM 
    Invoices IV
JOIN 
    TimeEntries TE on TE.InvoiceId = IV.InvoiceID
JOIN 
    Tasks TA ON TE.TaskID = TA.TaskID

I'm thinking the right way is to use a cursor and iterate through all records in the Tasks table, but I'm not sure how to proceed.

This is what I have so far:

DECLARE @ForeignKey int

DECLARE C CURSOR FOR SELECT IV.InvoiceID, TA.ProjectID FROM Invoices IV
JOIN TimeEntries TE on TE.InvoiceId = IV.InvoiceID
JOIN Tasks TA ON TE.TaskID = TA.TaskID

OPEN C

FETCH NEXT FROM C INTO @ForeignKey

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Invoices
    SET ProjectID = @ForeignKey
    WHERE InvoiceID = ??? (Need matching ID)

Thanks in advance!

Upvotes: 0

Views: 25

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You don't need a cursor for this:

UPDATE IV
    SET ProjectId = TA.ProjectID 
    FROM Invoices IV JOIN
         TimeEntries TE 
         ON TE.InvoiceId = IV.InvoiceID JOIN
         Tasks TA ON TE.TaskID = TA.TaskID;

Upvotes: 3

Related Questions