sql - Connecting column from one database with multiple columns from a different DB and returning an output in a new column

Question

I have a database of domains (list with domains -> example: thisisadomain.com) in one database and list of emails and number of clicks in a different database.

I need the domain list (from DB 1) to go through the second database and for every match record (Domain finds the same domain in the email. example: thisisadomain.com -> [email protected]) in the email the query also checks how many clicks there is in the same row for the matching domain=email.

Every email has the number of clicks in the same row (DB 2), there are also multiple emails with the same domain but different email address, these should be all counted as one in the end. All numbers of clicks have real value so for the emails with the same domain that number should be merged together to get a full number of clicks for all emails with same domain. There is only one number in the number of clicks that would need to be transformed from '65535' to number '1' since that is the value of 1 click. Note that database 2 can't be altered!

So the query should first find all of the emails that match with the domains from database 1. Then look up the number of clicks from every email, merge emails and NumOfClicks with the same email address into one domain ~ (continues bellow example)

Example:

Searching with domains row. One of the domains is also called domaindomain.com. The data bellow this blockquote is stored in database 2 in email column and the number next to the emails is the click count. The query found a match from DB 1 domain list!

 +-------------------------------+--------------+
 |            Email              |  NumOfClicks |
 +-------------------------------+--------------+
 |  [email protected]   |     2499     |
 +-------------------------------+--------------+
 |  [email protected]   |     65535    |
 +-------------------------------+--------------+

Table desc: Table from DB 2 that has Emails and NumOfClicks inside the table

Result:

The query merged together both Emails and NumOfClicks and outputted the data back in database 1. Database 1 has an empty column called NumOfClicks where the merged data of "clicks" should be imported. In this example the merged click count is 2500 although the numbers in the upper table are 2499 and 65535. The result down there is 2500, because 65535 is treated as NUMBER 1 and therefor it got transformed. 2499 + 1 = 2500

+-------------------------------+--------------+ 
|            Domain             |  NumOfClicks |
+-------------------------------+--------------+
|       domaindomain.com        |     2500     |
+-------------------------------+--------------+

~ and SUM all of the NumOfClicks together and output new number back to the database 1 in the column NumOfClicks next to the domain which the data goes for.

LEGEND

DATABASE1 before the query: **TABLENAME: DomainsActiV7**

 +-------------------------------+--------------+
 |            Domain             |  NumOfClicks |
 +-------------------------------+--------------+
 |        domaindomain.com       |              |
 +-------------------------------+--------------+
 |    stackoverflowislove.com    |              |
 +-------------------------------+--------------+
 |             ...               |              |
 +-------------------------------+--------------+

DATABASE2 before the query: **TABLENAME: ActiV7**

     +-------------------------------+--------------+
     |            Email              |  NumOfClicks |
     +-------------------------------+--------------+
     |     [email protected]     |     43156    |
     +-------------------------------+--------------+
     | [email protected] |      561     |
     +-------------------------------+--------------+
     |             ...               |              |
     +-------------------------------+--------------+

Answer 1

What about this?

Update [DB1 NAME]..DomainsActiV7
Set NumOfClicks = (
    Select SUM([DB2 NAME]..b.NumOfClicks)
        From [DB1 NAME]..DomainsActiV7 a
        Join [DB2 NAME]..ActiV7 b on a.Domain = RIGHT(b.Email, LEN(a.Domain))
)

And a second update to replace 65535 with 1

Update [DB1 NAME]..DomainsActiV7
Set NumOfClicks = 1
Where NumOfClicks = 65535