我想在某些条件下更新我的记录。
例如。我有一列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/