0

Morning folks, I'm a newbie at MC SQL. Need help wrapping my brain around the syntax of a SQL statement.

I have a Journey Builder (JB) Data Extension (DE) where I need the attendance field to be updated from another DE based on the Contact ID AND EVENT/ACTIVITY ID. So the source DE would be [Recruitment Contacts Booking Attended] and the Target DE would be [SC Contact Journey - November 26 2020 456 PM - 2020-11-27T105138104]. The following syntax I've tried with no success:

SELECT 
  T.[Event:Attendance__c]
, T.[Event:WhoId]
FROM [Recruitment Contacts Booking Attended] AS G
INNER JOIN [SC Contact Journey - November 26 2020 456 PM - 2020-11-27T105138104] as T 
  ON G.[Event:WhoId] = T.[Contact ID] AND G.[Event:Id]= T.[Activity ID]
WHERE G.Attendance = T.[Event:Attendance__c]
Improve this question
3
  • Please update your question tin include the error message you're getting.
    – Adam Spriggs
    Commented Dec 1, 2020 at 19:57
  • UPDATE! I've managed to create the SQL Query within Automation Studio. Using the following syntax below. No err messages. However, it's not updating the field. I do notice that the two DE's for Activity ID / Event:ID, one is 15 digits the other is 18. So I'm not sure if this is the issue or if I should be running the SQL query someplace else.
    – Robert Wynter
    Commented Dec 1, 2020 at 20:16
  • SELECT [SC Contact Journey].[Event:Who:Contact:Id],[SC Contact Journey].[Event:Id],[SC Contact Journey].[Event:WhoId], [Recruitment Contacts Booking Attended].[Attendance] as [Attendance_Status] FROM [SC Contact Journey] LEFT JOIN [Recruitment Contacts Booking Attended] ON [SC Contact Journey].[Event:Attendance__c]= [Recruitment Contacts Booking Attended].[Attendance] AND [SC Contact Journey].[Event:Id]= [Recruitment Contacts Booking Attended].[Activity ID]
    – Robert Wynter
    Commented Dec 1, 2020 at 20:23

1 Answer 1

Reset to default
0

Not only did I have the QRY wrong but I also had the DE coming from the Sales cloud report with a 15 digit Contact ID and the JB DE was 18. Once I adjusted this and tweaked the query the update worked.

SELECT a.[Event:Who:Contact:Id],a.[Event:Id],a.[Event:WhoId], b.[Attendance]  as [Event:Attendance__c]
FROM [SC Contact Journey - November 26 2020 456 PM - 2020-11-27T105138104] a
JOIN [Recruitment Contacts Booking Attended] b
ON a.[Event:Who:Contact:Id]= b.[Hidden Contact 18 Digit ID]
 and a.[Event:Id]= b.[Activity 18 Digit ID]
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .