CODEWITHSUNDEEP
sql-servert-sqlsql-updatesql-insert
kk1076
kk1076

Reputation: 1748

How to update and insert with list of values in sql

I get a list of values from the select query. From the list, I am checking whether the items in the list is available in the table. If the values exist, I need to update the values else Insert the list into the Table.

With the list, I can insert the list of values in the table. How to check and update the list in sql.

My Query :

 WITH pq AS
    (
    SELECT A.[ProductId] ,A.[Quantity],A.[OrderId],D.[ProductName],E.[SpecialPrice],E.[SpecialPrice]*A.[Quantity] AS SPrice FROM [Table1]  A
    LEFT JOIN [Table2]  B ON A.[OrderId] = B.[OrderId] INNER JOIN [Table3]  D
    ON A.[ProductId] = D.[ProductId] INNER JOIN [Table4] E
    ON A.[ProductId] = E.[ProductId] WHERE B.[CustomerId] = 1       
    AND A.[OrderId] = 77 
    )
    IF (EXISTS(SELECT [ProductId] FROM [Table5] WHERE [ProductId] = A.[ProductId]))
    BEGIN
    UPDATE [Table5]
    SET [Quantity] = A.[Quantity]
    WHERE B.[CustomerId] = 1 AND [ProductId] = A.[ProductId]
    END
    ELSE
    BEGIN
    INSERT INTO [Table5]
    ([ProductId],[ProductName],[Quantity],[Price],[TotalAmount])
    SELECT
    [ProductId],[ProductName],[Quantity],[SpecialPrice],SPrice
    FROM pq;
    END

Any suggestions will be greatly helpful.

EDIT : SELECT QUERY RESULT 

ProductId Quantity

   65       2

   64       1

Upvotes: 0

Views: 1235

Answers (1)

bartover
bartover

Reputation: 428

Assuming you're on SQL Server 2008 or above, the MERGE statement will solve your problem:

MERGE Table5 TRG
USING (
    SELECT
        A.ProductId,
        A.Quantity,
        A.OrderId,
        D.ProductName,
        E.SpecialPrice,
        (E.SpecialPrice * A.Quantity) SPrice
    FROM Table1 A
        LEFT JOIN Table2 B ON A.OrderId = B.OrderId
        INNER JOIN Table3 D ON A.ProductId = D.ProductId
        INNER JOIN Table4 E ON A.ProductId = E.ProductId
    WHERE
        B.CustomerId = 1       
        AND A.OrderId = 77 
) SRC
ON TRG.ProductID = SRC.ProductID
WHEN MATCHED THEN
    UPDATE SET TRG.Quantity = SRC.Quantity
WHEN NOT MATCHED BY TARGET THEN
    INSERT (
        ProductId
        , ProductName
        , Quantity
        , Price
        , TotalAmount
    )
    VALUES (
        SRC.ProductId
        , SRC.ProductName
        , SRC.Quantity
        , SRC.SpecialPrice
        , SRC.SPrice)
;

You can move the SELECT query out to a CTE for legibility like you did in your example.

Upvotes: 2

Related Questions