This is a followup to my previous question on this subject (http://www.itninja.com/question/sql-to-switch-user-ids). In summary: I want to switch the IDs of two users in the USER table instead of finding every possible table that ID might in. Since I didn't receive any answers regarding the SQL, I decided to see if fortune truly favors the bold.

I created a new ticket rule (the only way to write to the database to my knowledge) with the following SELECT statement:

SELECT * from `ORG1`.`USER`, `ORG1`.`HD_TICKET`
WHERE `USER`.`ID` = '6453' or `USER`.`ID` = '8206' or `USER`.`ID` = '999999999' and HD_TICKET.HD_QUEUE_ID = '1'

I added the HD_TICKET table in the SELECT to account for the face that KACE automatically pends "and HD_TICKET.HD_QUEUE_ID = '1'" to every SELECT query.

I then created my UPDATE query (assign old ID to temp ID, then new ID to old ID, then temp ID to new ID):

UPDATE `ORG1`.`USER`
SET `USER`.`ID` = '999999999'
WHERE `USER`.`ID` = '6453'; UPDATE `ORG1`.`USER`
SET `USER`.`ID` = '6453'
WHERE `USER`.`ID` = '8206'; UPDATE `ORG1`.`USER`
SET `USER`.`ID` = '8206'
WHERE `USER`.`ID` = '999999999';

Running this produces the following log:

mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE `ORG1`.`USER` SET `USER`.`ID` = '6453' WHERE `USER`.`ID` = '8206'; UPDAT' at line 6] in EXECUTE("UPDATE `ORG1`.`USER` SET `USER`.`ID` = '999999999' WHERE `USER`.`ID` = '6453'; UPDATE `ORG1`.`USER` SET `USER`.`ID` = '6453' WHERE `USER`.`ID` = '8206'; UPDATE `ORG1`.`USER` SET `USER`.`ID` = '8206' WHERE `USER`.`ID` = '999999999';")

Before you ask, I already asked KACE Support and they referred me to their Pro Service. I am trying to be cheap and avoid a $500+ service charge for basic SQL scripting.

Am I doing something wrong? Or am I simply attempting the impossible?

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Apparently, you can only run one update query at a time. So I created separate rules for each step. I still have to do it one user at a time, but it's a solution.

Answered 05/27/2014 by: tdickinson
Orange Senior Belt

Please log in to comment
Answer this question or Comment on this question for clarity