Tip: Reattributing tickets from one user to another via SQL query

[tfs]

I've changed ticket ownership a time or two by editing by hand in phpMyAdmin, but now I've got one where I need to change ownership about 50 tickets, and I'd like to do them all in one fell swoop.  As a prerequisite, I want to change ownership of the ticket, and the first reply on the ticket, but all of the other replies on the tickets should remain as if they were entered by the old user.

This way the new user will own the tickets, but will see the replies on the tickets made by the old user.

Old user = John Smith, userid = 23
New user = Fred Johnson, userid = 219

First, update the replies...

Code: [Select]

update smf_helpdesk_ticket_replies set id_member = 219, poster_name = 'Fred Johnson', poster_email = 'fjohnson@whatever.org' where id_msg in (select id_first_msg as id_msg from smf_helpdesk_tickets WHERE id_member_started = 23)
...Then update the tickets...

Code: [Select]

update smf_helpdesk_tickets set id_member_started = 219 WHERE id_member_started = 23
If you want to try this you'll need access to phpMyAdmin, and a stout heart.  Make sure to back it up first!  Edit the two SQL queries above, substituting your user id, name and email.  Do not attempt this if you're unsure... as it's for advanced users.  If you want help, just say the magic word.

[live627]

Why the sub-query?

update smf_helpdesk_ticket_replies set id_member = 219, poster_name = 'Fred Johnson', poster_email = 'fjohnson@whatever.org' where id_member_started = 23

[venguard223]

very good tip thx

your tip did not work live: #1054 - Unknown column 'id_member_started' in 'where clause'

[tfs]

Why the sub-query?

update smf_helpdesk_ticket_replies set id_member = 219, poster_name = 'Fred Johnson', poster_email = 'fjohnson@whatever.org' where id_member_started = 23

By doing it your way you will reattribute ALL replies from member 23 to member 219.  My example was to only reattribute the FIRST reply of each ticket, which is the ticket itself (not the actual replies to the ticket).  Notice my sub-query does a (select id_first_msg as id_msg).  That way when you look at the ticket, the replies from member 23 are still from member 23, but member 219 owns the ticket.

There's a method in the SimpleDesk UI to reattribute ALL tickets and ALL replies, by going to [Admin/Helpdesk/Maintenance] though I believe you must delete the old user first for that to work.

[tfs]

Some additional info to clarify for anyone finding this thread down the road.

When you first create a help desk ticket, but before you add any replies, it consists of two things.  An entry in (smf_helpdesk_tickets) and an entry in (smf_helpdesk_ticket_replies).  Notice that even though you've not made any replies yet, there's still a reply in (smf_helpdesk_ticket_replies).  That's a SPECIAL reply, and SimpleDesk treats it in a special manner, because it's the FIRST reply, and so it makes up the body of the ticket iteslf.

In other words: When you look at a ticket in SimpleDesk, what you see as the first reply on the ticket is actually the second reply in the database itself.  The first reply in the database is used as the body of the ticket.

How to tell which reply is a FIRST reply?  Easy.  In (smf_helpdesk_tickets) the field (id_first_msg) indicates the first message of the ticket, which SimpleDesk displays as the body of the ticket.  It finds the body by looking in (smf_helpdesk_ticket_replies) for the (id_msg) that is equal to the (id_first_msg) in (smf_helpdesk_tickets).