I have a number of tables that are basically log files of emails that have been sent to customers.
I need to work through each table and for each record update a master table that will contain summary details of whether the email was sent or not.
So as it works through each log:
1. If the email doesnt exist in the master table then add to the master table. If the email was sent add a '1' in the 'Sent' column and a '0' in the 'Fail' column, also add the log name in the 'LogID' column. Conversely if the email failed do the opposite.
2. If the email does exit in the master table then update that record. If the email was sent then add 1 to the value that already exists in the 'Sent' column and just make the 'Fail' column value '0'. If the email failed then make the 'Sent' column value '0' and add 1 to the value in the 'Fail' column.
This seems to me to be an iterative process that can't be performed with a single query. Am I right?