Обновить столбец на основе значения другого столбца
Мне нужно обновить столбец «ISP_GROUP» в моей таблице MySQL на основе значений столбца «EMAIL» в той же таблице.
Я сделал это в Excel. Я использовал приведенную ниже формулу для заполнения значений столбца ISP_GROUP на основе значений в столбце A, которые представляют собой EMAILS:
=IF(COUNTIF(A2,"*@gmail.com*"),"GM",
IF(COUNTIF(A2,"*@yahoo.com*"),"VMG",
IF(COUNTIF(A2,"*@aol.com*"),"VMG",
IF(COUNTIF(A2,"*@sbcglobal.net*"),"VMG",
IF(COUNTIF(A2,"*@att.net*"),"VMG",
IF(COUNTIF(A2,"*@ymail.com*"),"VMG",
IF(COUNTIF(A2,"*@rocketmail.com*"),"VMG",
IF(COUNTIF(A2,"*@aim.com*"),"VMG",
IF(COUNTIF(A2,"*@verizon.net*"),"VMG",
IF(COUNTIF(A2,"*@hotmail.com*"),"MSFT",
IF(COUNTIF(A2,"*@live.com*"),"MSFT",
IF(COUNTIF(A2,"*@msn.com*"),"MSFT",
IF(COUNTIF(A2,"*@outlook.com*"),"MSFT",
IF(COUNTIF(A2,"*@passport.com*"),"MSFT", "other" ))))))))))))))
Таким образом, приведенная выше формула будет учитывать адрес электронной почты в первом столбце. Затем, например, если адрес электронной почты является учетной записью «@gmail.com», установите для параметра ISP_GROUP значение GM.
Я больше не могу выполнить эту задачу в Excel, поскольку количество записей в CSV превышает объем, который может обработать Excel.
Итак, теперь мне нужно импортировать CSV непосредственно в мою таблицу MySQL, а затем обновить значения после их загрузки.
Я просто понятия не имею, как написать приведенную выше формулу в операторе обновления MySQL.
Итак, запрос начнется так:
UPDATE `table` SET `ISP_GROUP` = 'SOMETHING TO MIMIC THE ABOVE FORMULA';
Я не знаю, как преобразовать формулу Excel в запрос в MySQL.
На данный момент таблица выглядит так:
| EMAIL | ISP_GROUP |
-----------------------------
| [email protected] | |
| [email protected] | |
| [email protected] | |
| [email protected] | |
-----------------------------
Запрос, который мне нужно написать, обновит ISP_GROUP следующим образом:
| EMAIL | ISP_GROUP |
-----------------------------
| [email protected] | MFT |
| [email protected] | GM |
| [email protected] | VMG |
| [email protected] | OTHER |
-----------------------------
почему бы не создать справочную таблицу и не присоединиться к ним?
@tacoshy - вы предлагаете мне создать таблицу «ISP_GROUP», в которой будут храниться только разные типы интернет-провайдеров, а затем объединить их? Разве мне не нужно было бы иметь что-то, что будет отмечать каждый тип каждого электронного письма?
да, но тогда это будет однострочный запрос, который можно будет включить в исходный запрос SQL.
Это должен быть просто (относительно) простой оператор случая, не так ли? case when upper(<email>) like '%GMAIL.COM' then 'GM'...
Ответы 1
UPDATE `table`
SET `ISP_GROUP` =
CASE
WHEN `EMAIL` LIKE '%@gmail.com%' THEN 'GM'
WHEN `EMAIL` LIKE '%@yahoo.com%' THEN 'VMG'
WHEN `EMAIL` LIKE '%@aol.com%' THEN 'VMG'
WHEN `EMAIL` LIKE '%@sbcglobal.net%' THEN 'VMG'
WHEN `EMAIL` LIKE '%@att.net%' THEN 'VMG'
WHEN `EMAIL` LIKE '%@ymail.com%' THEN 'VMG'
WHEN `EMAIL` LIKE '%@rocketmail.com%' THEN 'VMG'
WHEN `EMAIL` LIKE '%@aim.com%' THEN 'VMG'
WHEN `EMAIL` LIKE '%@verizon.net%' THEN 'VMG'
WHEN `EMAIL` LIKE '%@hotmail.com%' THEN 'MFT'
WHEN `EMAIL` LIKE '%@live.com%' THEN 'MFT'
WHEN `EMAIL` LIKE '%@msn.com%' THEN 'MFT'
WHEN `EMAIL` LIKE '%@outlook.com%' THEN 'MFT'
WHEN `EMAIL` LIKE '%@passport.com%' THEN 'MFT'
ELSE 'OTHER'
END;
Пожалуйста, попробуйте приведенный выше код sql.
В принципе верно, просто несколько замечаний: 1) Позаботьтесь о корпусе. Безопаснее было бы написать LOWER(EMAIL). 2) В зависимости от количества строк это может быть очень медленно из-за всех этих LIKE условий. В этом случае было бы быстрее пропустить все выражение CASE и запустить отдельные команды обновления с каждым условием в виде предложения WHERE. 3) Этот уродливый способ исправить данные должен быть одноразовым и не повторяться. В будущем лучше улучшить структуру БД.
Те самые ВМГ можно сократить до WHEN EMAIL REGEXP '@yahoo.com|@aol.com|@sbcglobal.net|@att.net|@ymail.com|@rocketmail.com|@aim.com|@verizon.net' THEN 'VMG'...
Можете ли вы привести пример вашего вклада и ожидаемого результата?