CODEWITHSUNDEEP
sqlsql-server-2008
user3147183
user3147183

Reputation: 13

Update and Insert Query

I want to use insert and update together in a query how can i use it together for e.g 

INSERT INTO [Sales_Detail_Table] ([RecieptNumber], [ID], [Quantity] ) VALUES (?, ?, ?)
UPDATE       dbo.Medicine
SET           Quantity = ?
WHERE        (ID = ?)

i want to update the quantity in the medicine table i.e (Set medicine.quantity =Medicine.quantity -Sales_Detail_Table Quantity where ID = Sales_detail_table .ID entered )

Upvotes: 0

Views: 78

Answers (2)

John Chrysostom
John Chrysostom

Reputation: 4063

If you want to both insert and update, look into something like:

merge dbo.target main
using #updatedata new
ON main.ID=new.ID
WHEN MATCHED THEN
    UPDATE
    SET main.field=new.field
WHEN NOT MATCHED BY TARGET THEN
    INSERT (field)
    VALUES (new.field)

Upvotes: 0

mmilleruva
mmilleruva

Reputation: 2178

What do you mean you would like to use them together? If you want to ensure both actions happen, you can use a transaction for both the INSERT and UPDATE statements. This will ensure that both statements either fail or complete as a single unit of work.

Upvotes: 2

Related Questions