Update record in record in table

[pallem]

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]

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]

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]

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]

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]

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.