MySQL Update first instance of a record

Question

What im trying ot do is update the first instance of a record based on contact_id.

I have a table of email addresses and corresponding contact_id's. Each contact may have more than one email address so the table can contain more than one instance of the same contact_id.

I want to update the corresponding is_primary field to '1', when I hit the first instance of each distinct contact_id.

I'm not too concerned about what ends up being the primary email address as long as everyone in the database has one.

Table Example:

 contact_id   | email        |      is_primary
---------------------------------------------
 1001         | [email protected]      |0
 1001         | [email protected]   |0
 1002         | [email protected]     |0
 1003         | [email protected]     |0
 1003         | [email protected]         |0
 1003         | [email protected]   |0

So the result im looking for would be:

 contact_id   | email        |      is_primary
---------------------------------------------
 1001         | [email protected]      |1
 1001         | [email protected]   |0
 1002         | [email protected]     |1
 1003         | [email protected]     |1
 1003         | [email protected]         |0
 1003         | [email protected]   |0

Have tried creating a temp table and macthcing to that, but query updated all is_primary fields. I know im missing something basic here, but my sql skills are limited.

Chris Trahey · Accepted Answer · 2012-07-11 03:19:31Z

3

This technique joins the table against itself in a subquery, but it only matches one row (based on contact_id and email matching. The trick is that the subquery returns just one of the email addresses using MIN, theoretically the first one alphabetically (not reliable, but you said that didn't matter).

I have tested this with good results.

UPDATE 
  email 
  JOIN (SELECT contact_id, MIN(email) as email 
        FROM email GROUP BY contact_id) as singles 
  USING(contact_id, email) 
set is_primary=1;

answered Jul 11, 2012 at 3:19

Chris Trahey

18.3k1 gold badge44 silver badges55 bronze badges

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

3 Comments

losullivan Over a year ago

Thanks! Looks good so far. Will do some more tetsing, 5 mins or so. One more question, what if the same email address appears more than once, assigned to different contact_id's. This query should ignore that right?

Chris Trahey Over a year ago

Yes, if it's assigned to a different contact_id then it will be fine. That's what USING(contact_id, email) ensures (that is selects from the sob query based on matching both columns, not just the email).

losullivan Over a year ago

Everything checks out. Thanks mate, really got me out of a hole.

Collectives™ on Stack Overflow

MySQL Update first instance of a record

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related