使用子选择更新 MySql [英] MySql update with subselect

问题描述

我有一个 mysql 数据库表，其中有一个 email 列，最多可以包含两封用分号分隔的电子邮件.我想要实现的是将电子邮件字段中的第二个电子邮件地址写入另一列 email2这是我到目前为止没有成功的尝试:

I have a mysql database table where there is an email column which can contain up to two emails separated by an semicolon. What i want to achieve is write the second email address in the email field into another column email2 This is what I tried so far without success:

UPDATE user AS u
SET email2 = (SELECT SUBSTRING_INDEX(email, ';', -1)
              FROM user
              WHERE user.id=u.id)
WHERE username LIKE "%;%"

在搜索这个问题时，有一些解决方案在子查询中取消了第二个子查询，但没有一个真正符合我的问题.

When searching for this problems there were some solutions unsing a second subquery within the subquery but none of them really matched my problem.

如果有人有解决方案，请发布.我被困了至少 2 个小时.

If anybody has a solution please post it. I have been stuck for at leas 2 hours.

非常感谢.

推荐答案

无需让您的生活复杂化:

No need to complicate your life :

UPDATE user 
SET email2 = SUBSTRING_INDEX(email, ';', -1) 
WHERE username LIKE '%;%';

并清除同一查询中的第一列

and to clean first column in same query

UPDATE user 
SET 
  email2 = SUBSTRING_INDEX(email, ';', -1),
  email = SUBSTRING_INDEX(email, ';', 1),
WHERE username LIKE '%;%';

这篇关于使用子选择更新 MySql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！