Multiple update or inserts based on conditions
i am working on a trigger that checks if a user has a record in a table for a certain product.
1 If not insert a new record based on some criteria
2. If exisits, update it with the additional subscriptions.
NOTE: Records coudl be from 1 - 6 in value.
The code is basically a loop
something like this
How do i
select @product_update = (select count(product) from
Accounting_users_purchased goods where username = @user and product = @product--this product should be a variable representing products 1-6)
--The above line checks to see if the entry @product(which could be 1,2 ,3 to 6) exists then runs the codes below, then
if @product_update = 0
insert into Accounting_users_PurchasedGoods (product,quantity, username)
else update Accounting_users_PurchasedGoods
set quantity = (select quanity1 from Accounting_financials_PO_invoice where invoice_num = @invoiceID and username = @user)
1. Get this code to loop based on the values gotten in the above line in red.
NOTE: It checks to see if the products 1- 6 exists
2. Secondly, it updates the Accounting_users_purchasedGoods tables for each entry.
3. Do i use a union all operator to join multiple tables or just simply rewrite each code ??