SQL and MySQL Help

RockLobster · 14 Nov 2007 at 11:32

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?

RockLobster · 14 Nov 2007 at 12:20

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

voodooflux · 14 Nov 2007 at 12:25

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

RockLobster · 14 Nov 2007 at 12:37

Cheers will look at that. Will wait for other comments just to be sure. If I mess this update I'm dead.

voodooflux · 14 Nov 2007 at 12:45

RockLobster said:
If I mess this update I'm dead.

Take a backup first

And perhaps try it on a test database beforehand?

RockLobster · 14 Nov 2007 at 13:15

voodooflux said:
Take a backup first And perhaps try it on a test database beforehand?

We run backups every night thank god. I have tried your query with one row and it worked. Just need to take Moodle down for maintenance when the students have gone home.