SQL to switch User IDs
The situation: After the upgrade to v6 one of our techs ran a manual user import. It was misconfigured so that the LDAP UID was linked to samaccountname and username was linked to CN changing all of the usernames from, say, jsmith to John Smith, and breaking the link to the LDAP username, breaking the authentication for the effected users. The corrected import, instead of re-updating the username, created new users with the proper login names. However, this means that things like ticket ownership are broken, since those are linked by the ID field of the User table.
Now, I know just enough SQL to break something, but the script I came up with is:
/* Step 1: Change the old ID (1234) to correct username. */
SET `ID` = '1234'
WHERE `USER_NAME` = 'jsmith'; /* Step 2: Change the new ID (5678) to broken username. */
SET `ID` = '5678'
WHERE `USER_NAME` = 'John Smith';
The only way I know how to run SQL update queries is through Ticket Rules, so my questions are: Will this even work? Is there a better way? Is there a way to formulate a query to do this for all of the affected users (something along the logic lines of "IF John Smith and jsmith exist in USER_NAME, switch ID")?