January 18th, 2012, 11:46 AM
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
WHERE Quantity > 0
GROUP BY barcode
<!--- INSERT POSITIVE QUANTITY OUT INTO CART 1 --->
<cfquery name="InsertPositiveIntoCart" datasource="inventory">
INSERT INTO Cart1 (barcode, QuantityOut)
VALUES (#SumPositiveTemp.barcode#, #SumPositiveTemp.quantitysumPositive#)
January 18th, 2012, 03:14 PM
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?
January 18th, 2012, 04:26 PM
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'>
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.
January 18th, 2012, 05:41 PM
<cfif readTempTable.recordCount GT 0>
January 18th, 2012, 06:27 PM
.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?
January 18th, 2012, 09:24 PM
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.
January 18th, 2012, 09:42 PM
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.
<cfquery name="CompareTables" datasource='inventory'>
From Table b
Where barcode In (Select Barcode From Table a)
I get lost on what to do next.............
January 18th, 2012, 11:33 PM
Yes, that's much more useful information. You just need an inner join between the tables:
<cfquery name="checkExists" datasource="inventory">
From tableB b inner join tableA a ON a.barcode = b.barcode
<cfif checkExists.recordCount GT 0>
That's assuming you actually want do an insert if it exists in the temp table.
January 19th, 2012, 12:03 PM
Ok Thanks. Using .recordcount properly was what I was look for.