CODEWITHSUNDEEP
sql-serverstored-procedures
Jen143
Jen143

Reputation: 835

Update ItemId from Orderline table in stored procedure

I need to update the ItemId from the Orderline table.

Orderline table

OrderlineId 
SKU 
ItemId

Item table

ItemId 
SKU

This is the result I want to achieve

Orderline Table

OrderlineId  SKU      ItemId
1            SKUabc   NULL
2            SKU13    NULL

Item Table

ItemId       SKU
10           SKUabc
11           SKU13

RESULT:

Orderline Table

OrderlineId  SKU      ItemId
1            SKUabc   10
2            SKU13    11

So I need to get all data from Orderline with ItemId is NULL then loop that data then query Item table using Orderline.SKU then update Orderline.ItemId

Currently, I have this code but it is not working yet.

ALTER PROCEDURE [dbo].[UpdateOrderlinesItemId]
AS
    DECLARE @MyCursor CURSOR;
    DECLARE @ItemCursor CURSOR;
    DECLARE @SKU int;
    DECLARE @Item int;
BEGIN
    SET @MyCursor = CURSOR FOR
        SELECT 
            ol.OrderLineId, ol.ItemId
        FROM 
            [OrderLine] AS ol
        WHERE 
            ol.ItemId IS NULL

    OPEN @MyCursor 

    FETCH NEXT FROM @MyCursor INTO @SKU

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @ItemCursor = CURSOR FOR
            SELECT TOP 1 ItemId 
            FROM [dbo].[Item] 
            WHERE SKU = @SKU;

        CASE 
           WHEN @ItemCursor IS NOT NULL
              THEN UPDATE [dbo].OrderLine 
                   SET ItemId = @ItemCursor 
                   WHERE OrderLineId = @MyCursor.OrderLineId
        END

        FETCH NEXT FROM @MyCursor INTO @ItemId
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END

Please help me and thank you in advance.

Upvotes: 1

Views: 71

Answers (1)

Dale K
Dale K

Reputation: 27224

It seems to me that you just need a simple update:

update OL set
  ItemId = (
    SELECT TOP 1 I.ItemId 
    FROM dbo.Item I 
    WHERE I.SKU = OL.SKU
  )
FROM dbo.OrderLine OL
WHERE OL.ItemId IS NULL

Upvotes: 1

Related Questions