Update record in record in table (1 Viewer)

pallem

Registered User.
Local time
Today, 15:47
Joined
May 10, 2017
Messages
19
Hi, I have a record in table A that i want updated with the contents of a record in table B. The records in tables A and B have exactly the same layout. I feel there should be an easier way of doing this rather than using SET fieldA = FieldB for each field in the record in Table A?

Many thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:47
Joined
Oct 29, 2018
Messages
21,358
Unfortunately, I am not aware of any shortcut to do that. But my question is why do you have two tables with the same structure?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:47
Joined
Feb 28, 2001
Messages
27,001
If the tables have EXACTLY the same layout then why do you have two tables? There is a normalization principle that says that if two different things have identical structures, they really aren't two different things but rather are improper subdivisions of one thing.

You could merge the tables by adding a field that says "this record came from table A" or "from table B" (if that was really needed.) There is often something wrong with a design that requires this kind of data shuffling.

What are you trying to do? (Answer that in English descriptive terms, not in code.)

EDIT: Obviously, theDBguy is faster than I am at typing.
 

pallem

Registered User.
Local time
Today, 15:47
Joined
May 10, 2017
Messages
19
The record in the main table is copied to a temporary table so that values/paramters can be changed/played with to obtain the desired results. These values are then copied back to the main record in the main table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:47
Joined
Oct 29, 2018
Messages
21,358
The record in the main table is copied to a temporary table so that values/paramters can be changed/played with to obtain the desired results. These values are then copied back to the main record in the main table.

Hi. If the records in the temp table are to replace the ones in the main table, then jus delete the main table records first and then append the records from the temp table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:47
Joined
Feb 19, 2002
Messages
42,976
What happens if two people try to update the same record at the same time? There is no way you can reconcile and merge the results.

If you are going to do this, I would add a LockedBy field to the table. When a user indicates that he wants to update this record, update the lockedBY field to the user's ID to prevent someone else from updating the record. When the user applies his updates, set the LockedBy to null in the same update. You will also need a process for "unlocking" records and clearing out the temp version in the temp table should a user abandon an update mid process.
 

Users who are viewing this thread

Top Bottom