Search code examples
mysqlsqlcountinner-join

Update field in table with count of rows in another

I am building some custom email performance reporting in Marketing Cloud. I have one table which has a list of the emails sent in the last 30 days. It has a unique identifier, JobID. It has a few more columns where I want to insert for example how many times email was opened.

JobID   EmailName   Opens
37735   Test Email

I also have a table containing a list of all the opens of the emails sent in the last 30 days (using an inner join on the first table). I want to count the number of rows with job no. 37735 for example, and use this value to populate 'opens' in the above table.

JobID  Subscriber  DateOpened
37735  [email protected]  14/09/2017
37735  [email protected]  14/09/2017

So from the above, I want a '2' in the 'Opens' column of the first table. Can anyone help?

Solution

  • If I understand correctly, you want an update. If so:

    update table1 t1
    set opens = (select count(*)
                 from table2 t2
                 where t2.jobid = t1.jobid
                )
    where t1.jobid = 37735;