我想在某些条件下更新我的记录。

例如。我有一列ResidenceAddress1,其中包含电子邮件ID和地址。

样本数据:

sap3200@gmail.com,Rourkela
sap3212@gmail.com 2nd street,7 hills
2nd street, sap3212@gmail.com


我通过以下方式找到了来自ResidenceAddress1的电子邮件:

select (concat(trim(substring_index(substring_index(Residence_Address1, '@', '1'), ' ', -1)),'','@gmail.com') as mail,Residence_Address1
from mytable
where Residence_Address1 like '%gmail%' and Email_Personal1=""


当我这样更新时:

update mytable
set email_Personal1=concat(trim(substring_index(substring_index(Residence_Address1, '@', '1'), ' ', -1)), '','@gmail.com') ,
                 Residence_Address1=replace(residence_address1,"'concat(trim(substring_index(substring_index(Residence_Address1, '@', '1'), ' ', -1)), '','@gmail.com')'",'')
where Residence_Address1 like '%gmail%' and Email_Personal1=""


这样,它会更新email_personal1,但不会用该行中替换的空值而不是gmail id值来更新Residence_address1。我在哪里错了?

最佳答案

您可以更新多个列并在update中混合使用字符串函数。在问题中,您有多余的双引号,这是不必要的。也许您打算这样:

update fulldata_table
    set email_Personal1 = concat(trim(substring_index(substring_index(Residence_Address1, '@', '1'
                                       ), ' ', -1)
       ), '', '@gmail.com'),
        Residence_Address1 = replace(residence_address1, concat(trim(substring_index(substring_index(Residence_Address1, '@', '1'), ' ', -1)), '', '@gmail.com', '')
    where Residence_Address1 like '%gmail%' and Email_Personal1 = ''

关于mysql - 更新记录时我们可以在sql的替换函数中使用字符串函数吗,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/38565772/

10-09 20:21
查看更多