This is a followup to my previous question on this subject ( 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:

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):

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:
0 Comments   [ + ] Show Comments


Please log in to comment



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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja