Help in forming SQL Query

Question

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, prod2id, 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?

Conrad Frix · Accepted Answer · 2011-01-28 18:15:32Z

1

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

answered Jan 28, 2011 at 18:15

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arnaud Le Blanc · Accepted Answer · 2011-01-28 18:17:35Z

0

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)

answered Jan 28, 2011 at 18:17

Arnaud Le Blanc

100k24 gold badges211 silver badges196 bronze badges

1 Comment

mfr Over a year ago

Thank you for your reply. Table 1 is in a different db host than table 2. What I will need to do in that case?

Jakob · Accepted Answer · 2011-01-28 18:35:09Z

0

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;

answered Jan 28, 2011 at 18:35

Jakob

24.5k9 gold badges48 silver badges59 bronze badges

1 Comment

mfr Over a year ago

Thank you for your reply. Table 1 is in a different db host than table 2. What I will need to do in that case?

Collectives™ on Stack Overflow

Help in forming SQL Query

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related