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

Hi there everyone..
I want to make a routine add a new value to some blank fields and update
some old value on other fields in a recordset.  I can do it using program
code, but I wonder is it posible to do it with SQL.  I cannot figure out
the action query because it has to contain INSERTINTO and UPDATE command in
one query.  I tried the query but it doesnt work.  Can anybody help me?

TIA
Chris



Sat, 06 May 2000 03:00:00 GMT  
 update and insert query?

Quote:

>I want to make a routine add a new value to some blank fields and update
>some old value on other fields in a recordset.  I can do it using program
>code, but I wonder is it posible to do it with SQL.  I cannot figure out
>the action query because it has to contain INSERTINTO and UPDATE command in
>one query.  I tried the query but it doesnt work.  Can anybody help me?

INSERT is used to append NEW rows and at the same time set their attribute
values.
UPDATE is used to modify attribute values in EXISTING rows.

Could you give an example of what you're trying to do ?



Sun, 07 May 2000 03:00:00 GMT  
 update and insert query?

Sorry for not described my problem more clearly.

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?



Quote:

> >I want to make a routine add a new value to some blank fields and update
> >some old value on other fields in a recordset.  I can do it using
program
> >code, but I wonder is it posible to do it with SQL.  I cannot figure out
> >the action query because it has to contain INSERTINTO and UPDATE command
in
> >one query.  I tried the query but it doesnt work.  Can anybody help me?

> INSERT is used to append NEW rows and at the same time set their
attribute
> values.
> UPDATE is used to modify attribute values in EXISTING rows.

> Could you give an example of what you're trying to do ?



Mon, 08 May 2000 03:00:00 GMT  
 
 [ 3 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