Shane wrote:
> I am importing data from a student information system into MySQL. I
> need to create unique usernames in the format of "first.last". If
> there is a duplicate, I would like to be able to insert the username
> as "first.last[n]". Currently I have 262 duplicate combinations of
> first and last names.
>
> I imagine this can be done with some sort of looping structure, but I
> would like to ensure that it remains fast (currently importing 10,000+
> users in under 10 seconds).
>
> I am thinking of doing the following (not any particular language, but
> you should get the idea):
> <code>
> i = 1
> dups = false
> dupnum = 0
>
> dupnum = SELECT COUNT(*) FROM students s WHERE s.uername IS NULL
>
> if dupnum > 0 then
> dups = true
> else
> dups = false
> end
>
> Do while dups = true
> UPDATE IGNORE students s SET s.user_name = CONCAT(s.firstname, '.',
> s.lastname, i)
> dupnum = SELECT COUNT(*) FROM students s WHERE s.username IS NULL
> if dupnum >= 1 then
> dups = true
> else
> dups = false
> Loop
> </code>
>
> Any thoughts or better ideas?
>
> Thanks!
>

A PK should be independent of the data. What are you going to do if,
for instance, a student's name changes (i.e. gets married)?

Use an auto_increment column for the primary key instead. Or, if you
have a student id number, that would be a second choice.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================