MySQLUpdate记录的第一个实例-MySQLUpdatefirstinstanceofarecord

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

我尝试做的是基于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.

我有一个电子邮件地址表和相应的contact_id。每个联系人可能有多个电子邮件地址，因此该表可以包含同一个contact_id的多个实例。

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

当我点击每个不同的contact_id的第一个实例时，我想将相应的is_primary字段更新为“1”。

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         | john@world.com      |0
 1001         | desmond@world.com   |0
 1002         | person@life.com     |0
 1003         | preson@help.com     |0
 1003         | bad@sql.com         |0
 1003         | have@searched.com   |0

So the result im looking for would be:

所以我要找的结果是：

 contact_id   | email        |      is_primary
---------------------------------------------
 1001         | john@world.com      |1
 1001         | desmond@world.com   |0
 1002         | person@life.com     |1
 1003         | preson@help.com     |1
 1003         | bad@sql.com         |0
 1003         | have@searched.com   |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.

尝试创建一个临时表并进行macthcing，但查询更新了所有is_primary字段。我知道我错过了一些基本的东西，但我的sql技能有限。

1 个解决方案

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;