Can i get an update query to not add records to tables only update? (1 Viewer)

[King_kamikaze]

Hi Guys,

I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.

It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.

Any suggestion guys & gals?

Many thanks
Tim

 

[Len Boorman]

If you are updateing table 2 to table 1 why are you putting the records in table 2 to start with. After update you now have data duplication which means that your tables are not Normalised correctly.

Suggest Normalisation first and this may well remove the update need and therefore your problem.

Len B

 

[King_kamikaze]

Basically the problem is that a lot of information is imported into table2 daily and i need it to only update the information thats "reference" is in table one.

i.e table2 reference must exist in table one for it to update, it it does not dont just add it, do nothing.

I am not aware of Normalisation is this something that you think will help in this scenario.

 

[Len Boorman]

Normalisation is fundamental to the design of a database. Un Normalised databases are rife with problems and anomolies. Suggest you search the net and read up on the subject. You will also find quite a lot on the forum via the saerch

L

 

[King_kamikaze]

Thanks Len, i have i just read the following arctile of which you had some input in also.

http://www.access-programmers.co.uk/forums/showthread.php?t=88648&highlight=Normalisation

The database i have created will not need normalisation because it is not used for manual input, basically we have a system that kicks out a text file, i run a macro on it to get the data in the right format and then inport it so i guess you could consider the macro a form of normalisation.

Back to my original point though, is there anyway to prevent an update query appending records?

Thanxs a lot y'all

 

[Len Boorman]

Okay
What I think you need to do is to have a query generate a list of the references that you want updated.

I use a technique to sort out lists that uses UNION queries and then find duplicates or non duplicates.

Basically a UNION query is

Select fields
FROM table
UNION ALL
Select fields
FROM another table
UNION ALL

etc and close with ;

Rules are that number of fields in each select statement must be the same and in same sequence

Data types of fields must be compatible

So field 1 in select 1 maybe text so it is no good if field i in select 2 is a true/false field

This gives you a complete listing.

Example

Select Ref
FROM table 1
UNION ALL
Select Ref
FROM table 2;

So now you have all the refs from table 1 and all the refs from table 2 in one listing

If you now query this to find non duplicates you will find those that only appear in one of the tables.

Getting a bit smarter

Select Ref
FROM table 1
UNION ALL
Select Ref
FROM table 2
UNION ALL
Select Ref
FROM table 2;

Ensures that Table 2 refs are duplicated

Now a find non duplicates will list Refs in table 1 that are not in table 2 and you could maybe use this as the update control.

Might give you a few clues

L

 

[King_kamikaze]

Thats an excellent suggestion, i have had a little play with using a union query but to no joy, you mention "Find non duplicates" in what context would you acheive this?

Also to give you a little more infor on the layout, table1 is the main table with about 80000 records and table2 is the one where the information that needs to be updated from is kept (usually around 50 records).

So the 2 tables are actually massively difference in size, there layout is pretty much the same.

Do you still think using the union query could help me?

Cheers
Tim

 

[Mile-O]

you mention "Find non duplicates" in what context would you acheive this?

It's one of the Query Wizards.

I must admit I'm a little confused by the title of this thread. An update query does exactly that - it only updates record(s). An append query adds record(s) to tables.

 

[ScottGem]

Hi Guys,

I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.

It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.

Any suggestion guys & gals?

Many thanks
Tim

I'm confused here. An Update query ONLY updates existing records. An Update query should never add records to a table. Your SQL should look something like this:

UPDATE table1 SET table1.field = table2.field
WHERE table1.Reference = Table2.Reference

This should never add records to a table.

 

[Len Boorman]

King
Take note of the posts re Update or Append. The Union query with the Non Duplicates query acting on it is a technique to determine which records to be appended/Updates. Yopu then create an Update or Append query that has two objects. The table with the data you want to Append/Update and teh Non Dups query which determines which records are to be appended/Updates. Join the two objects across a common field (Ref) and then you can select the ref from the Non Dups query and the Data from the table and fields and specify the table the data is to be appended to.

Test it out on a copy database

L