How do I replace records in a table?

[Guest]

Hi,
Not sure if this can be done, but I have a table with about 10,000 records
in it, all have a Main ID#, I need to replace about 80 of the records
(various Main ID#s) with records having the same Main ID # (these records
were updated elsewhere, and now I want to replace the existing records). I
know Append won't work, how do I replace records and keep the MainID#s in
tact? Thanks much.

 

[Tom Ellison]

Dear Cissy:

I don't know whether I'm confused or not. I guess that's the worst kind of
confused!

It would sound like you want to reference them by ID value and then update
all the other columns.

Somehow, I doubt that's the information you intended to elicit, but you
don't give much for particulars, and I'm not sure what else you need to
know. The general answer to your question is to update. The update must
match the ID value, and, like I said, update all the other columns.

Tom Ellison

 

[Marshall Barton]

Not sure if this can be done, but I have a table with about 10,000 records
in it, all have a Main ID#, I need to replace about 80 of the records
(various Main ID#s) with records having the same Main ID # (these records
were updated elsewhere, and now I want to replace the existing records). I
know Append won't work, how do I replace records and keep the MainID#s in
tact?

I generally Delete the old records

DELETE M.*
FROM maintable As M INNER JOIN newtable As X
On M.ID = X.OD

and then append the new records.

 

[Tom Ellison]

Dear Marsh!

I agree this is best, except it appeared to me that they want to "keep the
MainID#" which would mean updating. I had your approach in my post at
first.

It is still possible this way, but you would not be able to assign
autonumbers as needed. You could alter the autonumber column to be long
integer, then put the values in, then change back to autonumber as an
alternative. Then your suggestion would satisfy the requirement. I just
though that, if it is necessary, would be more work than the update, and the
update is faster in most cases (IMHO).

Do you think I've got that right?

Tom

 

[Van T. Dinh]

I am guessing that you hace 2 copies of the Table of the same structure and
you want to update the data from the SourceTable to the DestTable.

In this case, you can simply use an Update Query like:

UPDATE [DestTable] AS D INNER JOIN
[SourceTable] AS S ON D.[Main ID#] = S.[Main ID#]
SET D.Field1 = S.Field1,
D.Field2 = S.Field2,
....

 

[Guest]

You've all given me a lot to work on, thanks much!

I am guessing that you hace 2 copies of the Table of the same structure and
you want to update the data from the SourceTable to the DestTable.

In this case, you can simply use an Update Query like:

UPDATE [DestTable] AS D INNER JOIN
[SourceTable] AS S ON D.[Main ID#] = S.[Main ID#]
SET D.Field1 = S.Field1,
D.Field2 = S.Field2,
....

--
HTH
Van T. Dinh
MVP (Access)

Hi,
Not sure if this can be done, but I have a table with about 10,000 records
in it, all have a Main ID#, I need to replace about 80 of the records
(various Main ID#s) with records having the same Main ID # (these records
were updated elsewhere, and now I want to replace the existing records).
I
know Append won't work, how do I replace records and keep the MainID#s in
tact? Thanks much.

 

[Marshall Barton]

No Tom, I don't think that's right. You can append records
into an autonumber field as long as the the added record's
value does not already exist in the table. Actually, I
think the restriction only applies if the autonumber field
has a unique index. There may be some other issues that
I've never seen, but I use this kind of syncing fairly
often.

The Update approach is a good way to do it too, probably
even faster. I just find it tedious to list out all the
oldtable.field = newtable.field, . . .
for every field in the table.

 

[Tom Ellison]

I see. Thanks, Marsh. I get this confused with the functionality in MSDE,
which is quite different.

Tom

No Tom, I don't think that's right. You can append records
into an autonumber field as long as the the added record's
value does not already exist in the table. Actually, I
think the restriction only applies if the autonumber field
has a unique index. There may be some other issues that
I've never seen, but I use this kind of syncing fairly
often.

The Update approach is a good way to do it too, probably
even faster. I just find it tedious to list out all the
oldtable.field = newtable.field, . . .
for every field in the table.
--
Marsh
MVP [MS Access]

I agree this is best, except it appeared to me that they want to "keep the
MainID#" which would mean updating. I had your approach in my post at
first.

It is still possible this way, but you would not be able to assign
autonumbers as needed. You could alter the autonumber column to be long
integer, then put the values in, then change back to autonumber as an
alternative. Then your suggestion would satisfy the requirement. I just
though that, if it is necessary, would be more work than the update, and
the
update is faster in most cases (IMHO).

Do you think I've got that right?