Question about inserting or updating

[former_member1021736]

I need to loop through all the warehouses in a given PLANT (by the given "PlantId") and total the transaction data into a summary "InventoryMovement" table:  I need to totalize, for each inventory item, the initial inventories, the purchases, sales, etc....

Using the "initial inventory" as an example.   For each warehouse, I need to INSERT into the "InventoryMovement" the initial inventory if the record doesn't exist, but UPDATE it if there already is a record for the given item. 

I'm in a mental block and can't figure out how to do this...

Parameters:   @PlantId     -  Plant we are working on

                    @InitialDate -  Initial date

   FOR WarehousesLOOP AS CURSOR_Warehouses

      CURSOR FOR

          SELECT WarehouseId       AS @WarehouseId

          FROM PlantWarehouses

          WHERE PlantId = @PlantId

      DO

            -------------------------------------------------------------------------------------------------

            -- INITIAL INVENTORY

            -------------------------------------------------------------------------------------------------

            INSERT INTO InventoryMovement

            ON EXISTING UPDATE

                  (PlantId,ItemId,MovementDate,InitialInventoryQty)

                  SELECT @PlantId,ItemId,InventoryDate,Quantity

                  FROM InventoryCount WHERE WarehouseId = @WarehouseId AND InventoryDate = @InitialDate

            --

         END IF;

   END FOR;

The above fails because for rows that already exists I don't want to just overwrite them, I need to ADD the existing's row InitialInventoryQty with the new warehouse's qty.

Does this make sense?    How do I do this sort of thing in SQL?

   Best regards,

   Edgard

[former_member182948 · Accepted solution]

Hello Edgard,

Probably, INSERT... ON EXISTING UPDATE would not solve it.

I think that the MERGE statement will meet your purpose.

The MERGE statement can describe different action in UPDATE and INSERT.

Please see the following online document.

"MERGE statement"

(ver.12)

http://dcx.sap.com/index.html#1201/en/dbreference/merge-statement.html

(ver.16)

http://dcx.sap.com/index.html#sa160/en/dbreference/merge-statement.html

(ver.17)

http://dcx.sap.com/index.html#sqla170/en/html/8171ce016ce21014b630c0acb78fa8a8.html

(Example)

-------------------------------------------------------------------------------------------------

-- INITIAL INVENTORY

-------------------------------------------------------------------------------------------------

MERGE INTO InventoryMovement

   USING (SELECT ItemId,InventoryDate,Quantity

      FROM InventoryCount WHERE WarehouseId = @WarehouseId AND InventoryDate = @InitialDate) as InvCnt

      ON InventoryMovement.PlantId = @PlantId

   WHEN MATCHED THEN UPDATE SET InitialInventoryQty = InitialInventoryQty + InvCnt.Quantity

   WHEN NOT MATCHED THEN INSERT( [column-lists] )

      VALUES ( [values] );

* The design of table and columns of the example will be wrong.

* Because I don't know your database schema and detailed goal.

Regards,

Koichi

[former_member1021736 · Accepted solution]

Hi Koichi,

I didn't know about the MERGE statement.   

Thanks for your help!

Best regards,

Edgard

[former_member182948]

For more information about the MERGE statement, please see the following document.

"From the Archives: Using the MERGE statement with SQL Anywhere"

http://scn.sap.com/community/sql-anywhere/blog/2014/02/12/using-the-merge-statement-with-sql-anywher...