Hello,
We are on RHEL 6.5, MySQL 5.1.7 & PHP 5.3.3 and running moodle 2.6.4+
Table: mdl_user;
We would like to populate the "idnumber" field with the same value in the "username" field globally.
How could we achieve this through an SQL statement?
I don't think we can use the cli syncronization script (which accesses our external database) for legacy ones already in moodle but we can use that script moving forward. Come to think of it, what happens if we run the script again?
Thank you everyone.
hmm... would this work?
Data:
----
select id, username, idnumber from mdl_user where idnumber = "" order by id desc limit 10;
+-------+-----------+----------+
| id | username | idnumber |
+-------+-----------+----------+
| 83336 | 00010000 | |
| 83255 | 00010001 | |
| 83254 | 00010002 | |
| 83253 | 00010003 | |
| 83252 | 00010004 | |
| 83251 | 00010005 | |
| 83250 | 00010006 | |
| 83249 | 00010007 | |
| 83248 | 00010008 | |
| 83247 | 00010009 | |
+-------+-----------+----------+
10 rows in set (0.00 sec)
-------------------
update mdl_user
set idnumber = username
Is that it?
Hi Jerry,
Yes, that would do it if you want to overwrite both empty and non-empty fields. To update only empty fields:
UPDATE mdl_user
SET idnumber = username
WHERE idnumber = "";
Thanks Mr. Madero
My concerns are would this affect any existing transactions in any of the tables such as quizzes, grades, forum participations, etc?
I don't think so as I am just updating a field with info that was once blank anyway.
Am I correct? Just thinking before if we make this change in production.
Thoughts?
Hi Jerry,
I don't believe it will affect any transactions.
My question is though, why? It will be something you need to do each time you add users. I have a vague recollection that I thought of doing exactly what you are recommending some time ago, however worked out another workaround. Perhaps the workaround I did would be more useful. Let me know your case and it may help to jog my memory.
Cheers
Peter
Thank you Peter. We have used the built in tool already to insert that data.
We are just making sure we want to update those records that don't have value in the idnumber field before we implemented the use of the builtin tool from moodle that's all. We only started using this a week ago. We have about 75,000 that are empty
Hi Jerry,
No, you needn't worry, it's just an informative field used to provide extra user information when needed.
