• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

update and insert records

Hello.
I would like to be able to update existing records and insert new records into a main table from another table.

Is this possible using append query to do the inserts and update query to change the data on existing records?

I have been trying to do an append query to append new records into the main table if they don't already exist another table. Then doing an update query to update the existing data in the main table?

How may this be done or is it better doing it with a macro or code

Thanks,
Ivan
0
icarey
Asked:
icarey
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to do this with 2 queries.
first, the update based on the join for the existing ones, and then insert those that are not yet in the table.
the update should be easy.
the insert requires a select like this one:
select b.*
  from table b
  left outer join a on (a.key = b.key) 
   where a.key is null

Open in new window

0
 
icareyAuthor Commented:
thanks angelIII

I have created the query ok but am unable to insert into the table due to field count

Tables STOCK and stock_new
Field names in both
NAME TITLE NAME2

SELECT stock_new.*
FROM stock_new LEFT JOIN STOCK ON stock_new.NAME2 = STOCK.NAME2
WHERE (((STOCK.NAME2) Is Null));

displays the new records

INSERT INTO STOCK(NAME,TITLE,NAME2)
SELECT stock_new.*
FROM stock_new LEFT JOIN STOCK ON stock_new.NAME2 = STOCK.NAME2
WHERE (((STOCK.NAME2) Is Null));

comes up with an error
Number of query values and destination fields are not the same

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this will do:
INSERT INTO STOCK(NAME,TITLE,NAME2)
SELECT stock_new.Names2, stock_new.Title, stock_new.Names2
FROM stock_new LEFT JOIN STOCK ON stock_new.NAME2 = STOCK.NAME2
WHERE (((STOCK.NAME2) Is Null));

Open in new window

0
 
icareyAuthor Commented:
Thank you angelIII your answer has help me greatly

Ivan
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now