MySQL 更新记录的第一个实例 [英] MySQL Update first instance of a record
问题描述
我尝试做的是根据 contact_id 更新记录的第一个实例.
What im trying ot do is update the first instance of a record based on contact_id.
我有一个电子邮件地址表和相应的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 的第一个实例时，我想将相应的 is_primary 字段更新为1".
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.
表格示例:
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
所以我要找的结果是:
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
已尝试创建临时表并对其进行处理，但查询更新了所有 is_primary 字段.我知道我在这里遗漏了一些基本的东西，但我的 sql 技能有限.
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.
推荐答案
这种技术在子查询中将表与自身连接起来，但它只匹配一行(基于 contact_id 和 电子邮件匹配.技巧是子查询仅返回使用 MIN 的电子邮件地址之一，理论上是按字母顺序排列的第一个(不可靠，但您说这无关紧要).
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;
这篇关于MySQL 更新记录的第一个实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
