SQL noob here and a bit out of my depth ;)

I have an update query that will change a Moodle account username using concat to add our organisations domain, essentially changing it to an email address:

UPDATE mdl_user SET username=CONCAT(username,'@ourdomain.ac.uk')

However, there is an issue as not all users use the same domain. I would like to modify this query to concat just the domain part from the user email field in the same mdl_user table. I guess another option would be to create a query that said make 'username'='email'?

Thank you,

Nige.

CodePudding user response：

You can use SUBSTRING_INDEX:

UPDATE mdl_user SET username=CONCAT(username, '@', SUBSTRING_INDEX(email, '@', -1))