Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Duplicate records in Table 1

Status
Not open for further replies.

dssjon

MIS
May 29, 2007
37
US
I need to duplicate records in a table based on an ID#
so I can expire the first copy and set the new records as current. I'm doing this because we cannot update records directly, we have to expire old records and update them using a duplicate of them.

So I have a 'status' and 'completion date' field where id=1
I need to update the status and date, but I need to make a copy of that record, re-insert into the same table, and set the current flag = true

Any ideas? Thanks
 
You may use a date flag instead of a 'yes/no' field.
This way, the 'current' records have the flag Null.
Whenever you want to 'duplicate the records, first flag the completion date with current (or any) date, then select those rows and insert them into the same table.

Something like

Update TableName Set FlagFieldName = Date() Where ID = 1 and FlagFieldName Is Null


After that....

Insert Into TableName (Field1, Field2, Field3...)
Select Field1, Field2, Field3... From TableName Where ID = 1 and FlagFieldName = Date()

Be sure NOT to include the FlagFieldName in the append query.

HTH

[pipe]
Daniel Vlas
Systems Consultant

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top