ansaurus

Question

Answer 1

+4 A:

It can be done without a loop yes:

UPDATE table1
SET Quantity = Quantity + 1
WHERE Item = @itemID

IF @@ROWCOUNT = 0
    INSERT INTO table1 (Item, Quantity)
    VALUES (@itemID, 1)

Codesleuth 2010-08-18 08:12:52

You are right but for your answer i have to use the loop of all the item and process one by one item than it is possible.

KuldipMCA 2010-08-18 10:06:58

My answer doesn't involve any loops. You might need to edit your question if there's another requirement here somewhere.

Codesleuth 2010-08-18 10:16:54

yes but for that you have to use IN clause like that WHERE ITEMID IN (Select ItemID from ITEMMASTER)

KuldipMCA 2010-08-18 10:34:07

@KuldipMCA you're commenting on the wrong answer.

Codesleuth 2010-08-18 10:40:33

Answer 2

+1 A:

FOR one row

 IF EXISTS (SELECT * from ItemMaster WHERE Item = @Item)
    UPDATE ItemMaster
      SET Quantity = @Quantity
    WHERE Item = @Item
 ELSE
    INSERT INTO ItemMaster VALUES(@Item, @Quantity)

For many rows:

 INSERT INTO ItemMaster (Item, Quantity)
 SELECT Item, Quantity
 FROM AnotherTable a
 WHERE NOT EXISTS (Select 1 from ItemMaster i Where i.Item = a.Item);

 UPDATE ItemMaster i
    SET Quantity = a.Quantity
 FROM AnotherTable a
 WHERE a.Item = i.Item

Michael Pakhantsov 2010-08-18 08:15:07

ansaurus

tags:

views:

answers:

Insert or Update without a loop?

related questions