#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    5
    Rep Power
    0

    How can I update or insert when comparing 2 tables?


    I have a table (temp) that inserts records into table (cart1) with no problem using the code below. But, the table (temp) records will change and I want to refresh the web page and have table(cart1) either keep inserting new records (if table(temp) "barcode" does not exist in table(cart1) using the code below, or Update table(cart1) if column(barcode) exists in table(cart1).

    I was playing with inner join to compare the two tables (barcode) but could not come up with a proper if statement (around the insert query) to only insert record if barcode did not exist in Cart1.

    Can anyone help? - Thanks.





    <!--- FIND POSITIVE QUANTITY OUT--->
    <cfquery name="SumPositiveTemp" datasource='inventory'>
    SELECT barcode, sum(quantity) as quantitysumPositive
    FROM temp
    WHERE Quantity > 0
    GROUP BY barcode
    </cfquery>


    <!--- INSERT POSITIVE QUANTITY OUT INTO CART 1 --->
    <cfoutput query="SumPositiveTemp">
    <cfquery name="InsertPositiveIntoCart" datasource="inventory">
    INSERT INTO Cart1 (barcode, QuantityOut)
    VALUES (#SumPositiveTemp.barcode#, #SumPositiveTemp.quantitysumPositive#)
    </cfquery>
    </cfoutput>
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    Not sure I follow. Can't you just query the temp table and based on whether there is a record returned do an update or an insert?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    5
    Rep Power
    0
    Yes, That was what I was look at doing but having problem using putting it into code.

    <!--- READ THE TEMP TABLE --->
    <cfquery name="ReadTempTable" datasource='inventory'>
    SELECT barcode
    From Temp
    </cfquery>
    Now, <cfif temp.barcode doesnt exist in Cart1 then proceed with insert <cfelse> Update????????????

    I'm close, but trying different cfif statements, I can't get it to work.
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    <cfif readTempTable.recordCount GT 0>
    insert
    <cfelse>
    update
    </cfif>

    ?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    5
    Rep Power
    0
    .recordcount will always be greater than 0 in the temp table. How do you use logic to compare table a to table b for the same barcode and the use a cfif statement?
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    Sorry but you're not explaining yourself so I still don't understand what you're trying to do. You can either use a join, or query the first table for the same ID/primary key value that you want to insert or update in the second table. But since I have no idea what your schema looks like I can't tell you specifically how to write the query.

    In fact, I really have no idea what this "temp" table is for, why you're using it, or how it relates to the other table.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    5
    Rep Power
    0
    I'm not explaining myself? Let me make it simple.

    1. Table a - column barcode
    2. Table b - Column barcode
    3. If table a barcode is not in table b , then insert.
    4. If table a barcode is in table b, then update.

    I don't know how much simpler I can make it. I know a query is needed and I already stated I was playing around with inner join. I need examples if someone knows how to do it.

    Like:
    <cfquery name="CompareTables" datasource='inventory'>
    Select Barcode
    From Table b
    Where barcode In (Select Barcode From Table a)
    </cfquery>

    I get lost on what to do next.............
  14. #8
  15. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,265
    Rep Power
    968
    Yes, that's much more useful information. You just need an inner join between the tables:

    <cfquery name="checkExists" datasource="inventory">
    Select Barcode
    From tableB b inner join tableA a ON a.barcode = b.barcode
    </cfquery>

    <cfif checkExists.recordCount GT 0>
    insert
    <cfelse>
    update
    </cfif>

    That's assuming you actually want do an insert if it exists in the temp table.
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2012
    Posts
    5
    Rep Power
    0
    Ok Thanks. Using .recordcount properly was what I was look for.

IMN logo majestic logo threadwatch logo seochat tools logo