batch updating

[elfudge35]

I have an endorsement type table linked to a master endorsement table with a foreign key

endorsement table
end_id
end_information

endorsement type
end_type_id
end_id
end_type

when users entered information to the form, the form was not properly linked and thus they did not see there were already records for those endorsements and they created duplicates, I have a list of the endorsement ids and their duplicates and I need to update the duplicates in the endorsement type table with the original endorsement id, is there a way I can do a batch update so I don't have to do it all manually

and there is also information that was entered in the endorsement table for the duplicate records that I need to transfer to the original records, is there any quicker way to do this than manually

 

[PHV]

Some UPDATE queries ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[elfudge35]

um, the point is there are thousands of records that I need to update with different values, if I have to do 3000 update queries I'll shoot myself

 

[PHV]

I have a list of the endorsement ids and their duplicates
Perhaps some elaboration on that could help us to help you ?

 

[elfudge35]

ok, I have a list of the ids and their duplicates

end_id dup_id
100001 100023
100002 100024
100003 100025
100004 100026

in the endorsement type table, I need to replace the duplicate foreign key with the original, (ie: 100023 needs to be changed to 100001 and so on, thousands of times)

also, in the endorsement table, I need all of the secondary information (effective date, effect, comment, etc) that is now on the duplicate end_id to be moved to the original end_id

end_id effectdate effect comment
100001 --/--/---- none none
100023 01/01/2000 delete replace

 

[PHV]

A starting point:
UPDATE [endorsement type]
INNER JOIN [list of dups] ON [endorsement type].end_id = [list of dups].dup_id
SET [endorsement type].end_id = [list of dups].end_id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[elfudge35]

wow, now I do feel like an idiot, thanks, I didn't even think of referring to my duplicate records query to get the data for the update query, sorry, brain fart