I have 2 De:
- XYZ Customers
- XYZ Bookings
Within both DE there is an email field with the below names:
- XYZ Customers email field is called 'EmailAddress'
- XYZ Bookings email field is called 'Cust_Email'
I have a Bookings_Count (number) field in the XYZ Customers DE.
I want to run an SQL Query Activity to count the number of Bookings that match against the customers Email address in the XYZ Bookings DE and save this into the XYZ Customers.Bookings_Count field.
This is my solution:
select count (Cust_Email) as [XYZ Customers.BookingCount] from [XYZ Bookings] where [XYZ Customers.EmailAddress] = [XYZ Bookings.Cust_Email]
but I get the below validation error:
An error occurred while checking the query syntax. Errors: Invalid column name 'XYZ Customers.EmailAddress'.
Any help greatly appreciated!
FYI: The column name
- XYZ Customers.EmailAddress
is perfectly valid and is in use in other queries - I think the error message is because this table isn't previously in use in this query?
Do I need to approach this differently?
I got the code to validate in the below format:
select count (Cust_Email) as [XYZ Customers.BookingCount], Cust_Email as [XYZ Customers.EmailAddress] from [XYZ Bookings] LEFT JOIN [XYZ Customers] ON [XYZ Bookings].Cust_Email = [XYZ Customers].EmailAddress Group by Cust_Email
but it wouldn't update the DE as it wouldn't use the email to match the row to update - not sure how to fix that?
I managed to find a work around but I'm keen to see if a better solution exists.
My work around was to:
- Create an intermediate DE with just 2 columns, bookings & cust_email.
Made an SQL query activity to save the total booking count & email address into the intermediate DE
select count (Cust_Email) as bookings, Cust_Email from [XYZ Bookings] WHERE Cust_Email IS NOT NULL Group by Cust_Email
Created a second query activity to join the booking count data to the original DE matching on email address.
Select Bookings as BookingCount, Cust_Email as EmailAddress from [Booking Totals] as B LEFT JOIN [XYZ Customers] as C ON C.EmailAddress = B.Cust_Email
Hope someone can show me a better solution that does it all in one query and without the need for an intermediate DE
Thanks for reading