CODEWITHSUNDEEP
sqlsql-server
Kamil Budziewski
Kamil Budziewski

Reputation: 23107

Select where tuple in statement

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.

Upvotes: 2

Views: 1109

Answers (6)

roman
roman

Reputation: 117475

update MailSubscription set
    BitField = 1
from MailSubscription as MS
where
    exists
    (
        select *
        from #temp2 as T2
            inner join #temp as T on T.EmailAddress = T2.EmailAddress
        where
            T2.UserProfile_Id = MS.UserProfile_Id and
            T.Service_Id = MS.Service_Id
    )

Upvotes: 0

Matthias
Matthias

Reputation: 164

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)

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Another option with EXISTS operator

UPDATE MailSubscription
SET BitField = 1
WHERE EXISTS (
              SELECT 1 
              FROM #temp2 t2 JOIN #temp t ON t2.EmailAddress = t.EmailAddress
              WHERE t2.UserProfile_Id = MailSubscription.UserProfile_Id
                AND t.Service_Id = MailSubscription.Service_Id
              )

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

UPDATE M
SET M.BitField=1
from MailSubscription M
inner join #temp2 t2 on M.UserProfile_id=t2.UserProfile_Id
inner join #temp t on M.Service_id=t.ServiceId
and t.EmailAddress=t2.EmailAddress

Upvotes: 3

Andomar
Andomar

Reputation: 238176

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

Upvotes: 2

bummi
bummi

Reputation: 27385

UPDATE MailSubscription SET BitField=1
FROM #temp2
JOIN #temp on #temp2.EmailAddress=#temp.EmailAddress
WHERE MailSubscription.Service_id = #temp.ServiceId 
  AND MailSubscription.UserProfile_id =  #temp2.UserProfile_Id

Upvotes: 2

Related Questions