1. For remove or replace Email from database for all industry type use below query -
UPDATE alarmmaster am JOIN devicemaster dm ON am.deviceid = dm.deviceid JOIN plantmaster pm ON pm.plantid=dm.plantid
JOIN industrytypemaster im ON pm.IndustryType = im.IndustryTypeId
SET am.EmailAdd = REPLACE(EmailAdd,'Email ID which need replace;','Email ID with whom need to replace;')
Example,
Remove Email ID - For remove Email ID keep blank in second colleen.
UPDATE alarmmaster am JOIN devicemaster dm ON am.deviceid = dm.deviceid JOIN plantmaster pm ON pm.plantid=dm.plantid
JOIN industrytypemaster im ON pm.IndustryType = im.IndustryTypeId
SET am.EmailAdd = REPLACE(EmailAdd,'pkgupta.cpcb@nic.in;','')
Replace Email ID - For replace Email ID in first colleen put which need replace and second colleen with whom need to replace.
UPDATE alarmmaster am JOIN devicemaster dm ON am.deviceid = dm.deviceid JOIN plantmaster pm ON pm.plantid=dm.plantid
JOIN industrytypemaster im ON pm.IndustryType = im.IndustryTypeId
SET am.EmailAdd = REPLACE(EmailAdd,'pkgupta.cpcb@nic.in;','vivekpcb@gmail.com;distillery.cpcb@nic.in;')
2. For remove or replace Email ID from database for particular industry type use below query -
UPDATE alarmmaster am JOIN devicemaster dm ON am.deviceid = dm.deviceid JOIN plantmaster pm ON pm.plantid=dm.plantid
JOIN industrytypemaster im ON pm.IndustryType = im.IndustryTypeId
SET am.EmailAdd = REPLACE(EmailAdd,'Email ID which need replace;','Email ID with whom need to replace;') WHERE im.IndustryType = 'Industry Type';
Example,
Remove Email ID- For remove Email ID keep blank in second colleen and add respective Industry type.
UPDATE alarmmaster am JOIN devicemaster dm ON am.deviceid = dm.deviceid JOIN plantmaster pm ON pm.plantid=dm.plantid
JOIN industrytypemaster im ON pm.IndustryType = im.IndustryTypeId
SET am.EmailAdd = REPLACE(EmailAdd,'pkgupta.cpcb@nic.in;','') WHERE im.IndustryType = 'Distillery';
Replace Email ID- For replace Email ID in first colleen put which need replace and second colleen with whom need to replace and add respective Industry type.
UPDATE alarmmaster am JOIN devicemaster dm ON am.deviceid = dm.deviceid JOIN plantmaster pm ON pm.plantid=dm.plantid
JOIN industrytypemaster im ON pm.IndustryType = im.IndustryTypeId
SET am.EmailAdd = REPLACE(EmailAdd,'pkgupta.cpcb@nic.in;','vivekpcb@gmail.com;distillery.cpcb@nic.in;') WHERE im.IndustryType = 'Distillery';
Note :- you can add multiple mail ID's with use (;) at end of Email