SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple update or inserts based on conditions

    hello,
    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

    Code:
    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)
    values (@item1,@quantity1,@user)
    else update Accounting_users_PurchasedGoods 
    set quantity = (select quanity1 from Accounting_financials_PO_invoice where invoice_num = @invoiceID and username = @user)
    How do i

    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 ??

    thanks
    Afrika

  2. #2
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi
    the summary of the above is as follows

    Code:
    1. Check to see if product 1 exist...,
    run update/insert code
    
    2. check for product 2 
    run update/insert code
    
    3. check for product 2 
    run update/insert code
    
    EXIT
    i know in VB script it would be a for each next loop

    thanks
    Afrika

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    tip: any time you think you need to do some database statement inside a loop, you might have a poor table design, or poor sql approach

    but that's all the advice i can offer, because i really don't understand what you're doing or why

    my philosophy is that you should not need to do a SELECT before attempting an UPDATE or INSERT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh sorry, I guess i didnt put my full db code.
    There is a select to check to see if the value exists.
    am using

    if exists (select * from ...) -- Check to see if product 1 exist...,
    run update/insert code into the purchased goods table

    this checks for each number of items in the invoice table which could be a maximum of 6 items.

    Based on the user selection, it updates he's purchased goods table or if he has none, it inserts it.

    hope u do understand
    rgds

  5. #5
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Which database system are you using? MS SQL?

    Code:
      @var1 = select true from t where t.productname = THETEST
      if @var1=true then
      	update t2 set a = b where c = d
      end if
    You need to use T-SQL in jobs / stored procedures / triggers / functions for creating application logic.

  6. #6
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    EXCELLENT,
    Thanks a lot asterix, its just what trying to do

    Yes its MS SQL i am using

    however it throws an error;
    error message is

    Code:
    Server: Msg 170, Level 15, State 1, Procedure Financials_PO_Core, Line 168
    Line 168: Incorrect syntax near '@var1'.
    Server: Msg 156, Level 15, State 1, Procedure Financials_PO_Core, Line 173
    Incorrect syntax near the keyword 'select'.
    Server: Msg 156, Level 15, State 1, Procedure Financials_PO_Core, Line 177
    Incorrect syntax near the keyword 'end'.

    rgds
    Afrika
    Last edited by afrika; Feb 14, 2005 at 08:35.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •