SQL and MySQL Help

Associate
Joined
2 Sep 2007
Posts
2,001
I have a table in our MySQL DB called mdl_user, this table contains username, firstname and surname. There's also a load of other fields. The students can edit their firstname and surname via a profile form in Moodle. The problem is a lot of the students have used nicknames, etc in their firstname and surname. So you might have: -

Username Firstname Surname
135634Crock Davey Trigger

My manager isn't happy with this, he wants their correct name. I have another table called correctinfo which has just three fields Username, Firstname and Surname. This table has the correct info, I imported this data from our Oracle database. What I want to do is create an Update query which looks at the correctinfo table and updates mdl_user table with their correct firstname and surname using the Username. Any ideas?
 
Last edited:
Can someone check the following query would be much appreciated.

UPDATE mdl_user
SET firstname.mdl_user = firstname.correctinfo, surname.mdl_user = surname.correctinfo
FROM mdl_user, correctinfo
WHERE mdl_user.username = correctinfo.username
FROM mdl_user, correctinfo
 
Something like this I think (untested):

Code:
update mdl_user
inner join correctinfo
on mdl_user.Username = correctinfo.Username
set mdl_user.Firstname = correctinfo.Firstname, mdl_user.Surname = correctinfo.Surname
 
Last edited:
Back
Top Bottom