update and insert query? 
Author Message
 update and insert query?

I have  2 tables: PODetail and InventoryItem

The structure of the tables (simplified):

PODetail :
PurchaseOrderID : Integer (PrimaryKey)
ItemNumber      : Text (PrimaryKey)
Price   :  Currency
UpdatedFlag  : Boolean (Default Value : False)

Inventory Item:
ItemNumber : Text (PrimaryKey)
Price :Currency

The Item Number in PODetail can be a an reorder item (that already has an
item number in InventoryItem) and can be a new item.  In the case of the
new item, I have to add a new item number on InventoriItem AND change the
UpdatedFlag field in PODetail  to TRUE so the record wont be updated twice.
Like I said before I already done that with program code (looping through
recordset), but I wonder is it posible to do that with SQL, because in the
action query I think we cannnot combine the INSERT INTO and UPDATE in one
statement.
The reason about my interest in using SQL is because in my experience the
performance using SQL is better than using program code.
Has anybody have any idea to the query or should I stick with program code?



Mon, 08 May 2000 03:00:00 GMT  
 update and insert query?

Quote:

> I have  2 tables: PODetail and InventoryItem

> The structure of the tables (simplified):

> PODetail :
> PurchaseOrderID : Integer (PrimaryKey)
> ItemNumber      : Text (PrimaryKey)
> Price   :  Currency
> UpdatedFlag  : Boolean (Default Value : False)

> Inventory Item:
> ItemNumber : Text (PrimaryKey)
> Price :Currency

> The Item Number in PODetail can be a an reorder item (that already has an
> item number in InventoryItem) and can be a new item.  In the case of the
> new item, I have to add a new item number on InventoriItem AND change the
> UpdatedFlag field in PODetail  to TRUE so the record wont be updated twice.
> Like I said before I already done that with program code (looping through
> recordset), but I wonder is it posible to do that with SQL, because in the
> action query I think we cannnot combine the INSERT INTO and UPDATE in one
> statement.
> The reason about my interest in using SQL is because in my experience the
> performance using SQL is better than using program code.
> Has anybody have any idea to the query or should I stick with program code?

Hi Christian,

Recently I built a database-intensive data manipulation suite and had to
make a similar decision as you are facing.  On a 100,000 record table,
the difference between using DB.Execute SQL statements and looping
through the recordset was massive.  SQL was 7 times faster than the
recordset loop.

In this case, you can easily experiment with pretty simple SQL
statements.  At first glance, you might consider a 4-step approach:

1 - Create a temporary table using INTO which is an extract of 'new
items'
2 - Base an INSERT INTO statement on this table to add items to
Inventory
3 - Base an UPDATE query on the same temp table to now update the
UpdatedFlag
4 - Run a normal UPDATE query on reorder items

Despite there being 4 processes in this little lot, I am pretty certain
that you will experience a considerable increase in performance.

Cheers,

Dan



Tue, 09 May 2000 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. update and insert query?

2. Update and Insert queries work in SQL but not in Access

3. Dialog box displayed during query insert or query update

4. inserting character in an access field by using an update query

5. oledb: foxpro index file not updating after inserts and updates

6. VB6,Access97,ADO - Update query doesn't update all records

7. UPDATE query not updating records

8. Update query using joined query as source

9. Update and insert with value in variables

10. Update / Insert record confirmation prompts

11. INSERT vs UPDATE

12. Automatically insert multiple JPG pictures into report - Updated Question

 

 
Powered by phpBB® Forum Software