How do I renumber Student Id numbers

How do I renumber Student Id numbers

by Mark Fellowes -
Number of replies: 4

We recently created a test student with an Id number that was much larger than the existing students (999999 instead of the next number in series 10001)

Can someone please provide a SQL update query that can renumber the incorrect student id's to follow suit of the original numbers.

And also if this is not available, which tables will have a relationship to this student id so I ensure all relationships tables / rows are updated.

Thankyou

Average of ratings: -
In reply to Mark Fellowes

Re: How do I renumber Student Id numbers

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
You can see how tables are related here
http://www.examulator.com/er
But those id values are supposed to be arbitrary and should not be used for humans to identify, use remember or value, they are for computers. I doubt if it is realistically possible to achieve what you want.
Average of ratings: -
In reply to Marcus Green

Re: How do I renumber Student Id numbers

by Mark Fellowes -
Hi Marcus,
Thanks for your link etc.. the scheme diagrams are hmmmmm very large giving the number of tables they hit... Everything is possible... wink I can update them manually but didn't want to miss a table / relationship. I work with SQL/DB2 for many years and we had to overcome this issue occasionally. Just needed the tables storing the stduent id's.. thankyou again
Average of ratings: -
In reply to Marcus Green

Re: How do I renumber Student Id numbers

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
To echo what Marcus has said here, changing a single student's id would require updating records across hundreds of database tables. It is an impractically huge task.

The closest option available would be to somehow create a new user account with the id that you require (which will be tricky as the id generation is done by the underlying database, not by the Moodle code itself) and then use the 3rd party Merge Users plugin to merge the accounts together. This is not recommended.

Even if you did manage to "solve" this problem (which isn't really a problem in the first place) the very next user generated would still have the large id value that you are trying to avoid.
Average of ratings: Useful (1)
In reply to Mark Fellowes

Re: How do I renumber Student Id numbers

by Michael Milette -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers Picture of Translators
Hi Mark,

A user id in Moodle is a reference to many other tables in a relational complex level database. The id field is also an auto-incremented number.

Have you considered using a different field for your student ID number? If you don't care for any of the existing built-in ones, you could create custom profile field. If you need it to be auto-incrementing for each student, you could even create your own custom profile field type for this. That way, it would work independently from Moodle's record ID number.
Average of ratings: -