Question: I have table with two FK `UserProfile_Id` and `Service_Id`. This table contains bit field which value I need to change. 

I have two temporary tables:

First table **#temp2**:

```
EmailAddress,
UserProfile_Id

```

Second table **#temp**:

```
EmailAddress,
Service_Id

```

This statement does not work:

```
UPDATE MailSubscription SET BitField=1
where UserProfile_id IN ( SELECT UserProfile_Id from #temp2 ) 
      and Service_id IN ( SELECT ServiceId from #temp)

```

I know why it does not work, but have no idea how to fix it to work fine. 

I need to change `bitField` for `MailSubscription` where **tuple**(UserProfile\_Id,Service\_Id) is in joined #temp and #temp2, but I can not write it like this in mssql.

Answer: 
You could use a filtering join:

```
update  m
set     BitField = 1
from    MailSubscription m
join    #temp t1
on      t1.Service_id = m.Service_id
join    #temp2 t2
on      t2.UserProfile_Id= m.UserProfile_Id
        and t1.EmailAddress = t2.EmailAddress

```
I think this should help u find the answer.

```
Update 'Tablename'
SET Mailsubscription = 1
WHERE concat(UserProfile_Id ,".", Service_Id) IN ( 
                        SELECT concat(t.UserProfile_Id , "." , t2,Service_Id)
                        FROM #temp t INNER JOIN #temp2 t2 
                               ON t2.EmailAddress = t.EmailAddress)

```