Help in forming SQL Query

I need some help to build a SQL query (in MySQL). I have two tables. Table 1 and Table 2 are sitting on different db host/schema.

Table1 has custid, tag, prod1id, prod2id, prod3id Table2 has custid, prodid

Now the query is to update the tag field of Table1. The field needs to be 1 if prod1id, p开发者_开发知识库rod2id, prod3id entries also exists in Table2 for a certain customer. Here is an example

Table1

custid, tag, prod1id, prod2id, prod3id

1       1    12       13       14
2       0    24       25       26

Table2

custid, prodid
1       12
1       13
1       14
2       24
2       26

The tag field for customer #2 is 0 because prod2id which is 25 does not exist in Table2. Could someone help me in forming the query?

Something like this should work (I don't have MySQL in front of me) so the case statement may need some work

 UPDATE 
     Table1
 SET 
     tag = Case 
                  WHEN t2p1.custid IS NOT NULL 
                       and t2p2.custID IS NOT NULL 
                       and t2p3.custId IS NOT NULL then 1
                  ELSE 0
                 END



FROM
     table1 t1
 LEFT JOIN Table2 t2p1
 on t1.custid = t2p1.custid   
    and t1.prod1id = t2p1.prodid

 LEFT JOIN Table2 t2p2
 on t1.custid = t2p2.custid
    and t1.prod2id = t2p2.prodid

 LEFTJOIN Table2 t2p3
 on t1.custid = t2p3.custid
    and t1.prod3id = t2p3.prodid

This should work:

UPDATE Table1 t1
    SET tag=1 
    WHERE EXISTS(SELECT 1 FROM Table2 t2 WHERE t2.custId = t1.custId AND t2.prodId = t1.prod1Id)
    AND EXISTS(SELECT 1 FROM Table2 t2 WHERE t2.custId = t1.custId AND t2.prodId = t1.prod2Id)
    AND EXISTS(SELECT 1 FROM Table2 t2 WHERE t2.custId = t1.custId AND t2.prodId = t1.prod3Id)

Here's a way where we don't have to do a join (or exists) for every prodid:

UPDATE table1, (SELECT table1.custid, (COUNT(*) = 3) AS tag
                  FROM table1 JOIN table2 ON table1.custid = table2.custid
                 WHERE table2.prodid IN (table1.prod1id, table1.prod2id, table1.prod3id)
              GROUP BY table1.custid) newtags
SET table1.tag = newtags.tag
WHERE table1.custid = newtags.custid;