Reprocessing modified emails

Message Share Topic Printable Version Google Delicious Digg StumbleUpon Windows Live Yahoo Bookmarks reddit Facebook MySpace Newsvine Furl Topic Search Topic Options Post Reply Create New Topic

   Hi guys,
Having a small problem dealing with modified emails being processed. Basically we receive flight itineraries which are processed in a few separate parses, the first grabs the bookingref, travellers name and then inserts in the bookings table and the second repeating block parse grabs each leg of the flight and inserts in to the flights table along with the booking ref from the first parse.  
The problem is if an Itinerary is updated it is resent to us and is processsed again. Now the booking ref in the bookings table is unique and therefore is not affected but then the flights are reprocessed and added to the flights table (meaning duplicate entries of which some data is outdated). 
I need to basically set it up so that when an Itinerary comes in if it has a Booking Ref that already exists then delete all entries with the same BookingRef from the flights table and continue processing. 
If I put a delete from Flights where bookingref=123 in the "Modified SQL" section of the flight parse this runs each time the parse is called and wipes out the previous data. If i run the modified statement from the inital parse it does not seem to touch the flights table at all. 
Any simple methods of achieving this or advice?

Author	Message Share Topic Printable Version Google Delicious Digg StumbleUpon Windows Live Yahoo Bookmarks reddit Facebook MySpace Newsvine Furl Topic Search Topic Options Post Reply Create New Topic
scalnon Members Profile Send Private Message Find Members Posts Add to Buddy List New User Joined: 17 Feb 2012 Posts: 2	Post Options Post Reply Quote scalnon Report Post Quote Reply Topic: Reprocessing modified emails Posted: 20 Feb 2012 at 4:08am
	Hi guys, Having a small problem dealing with modified emails being processed. Basically we receive flight itineraries which are processed in a few separate parses, the first grabs the bookingref, travellers name and then inserts in the bookings table and the second repeating block parse grabs each leg of the flight and inserts in to the flights table along with the booking ref from the first parse. The problem is if an Itinerary is updated it is resent to us and is processsed again. Now the booking ref in the bookings table is unique and therefore is not affected but then the flights are reprocessed and added to the flights table (meaning duplicate entries of which some data is outdated). I need to basically set it up so that when an Itinerary comes in if it has a Booking Ref that already exists then delete all entries with the same BookingRef from the flights table and continue processing. If I put a delete from Flights where bookingref=123 in the "Modified SQL" section of the flight parse this runs each time the parse is called and wipes out the previous data. If i run the modified statement from the inital parse it does not seem to touch the flights table at all. Any simple methods of achieving this or advice?

scalnon Members Profile Send Private Message Find Members Posts Add to Buddy List New User Joined: 17 Feb 2012 Posts: 2	Post Options Post Reply Quote scalnon Report Post Quote Reply Posted: 22 Feb 2012 at 6:50am
	Just to clarify we have resolved the issue. We had realised that due to the key field being set on the first parse the Update statement was being used instead of the Insert. Once I added the additional SQL commands to the Update statement everything ran as planned.