/build/static/layout/Breadcrumb_cap_w.png

SQL to switch User IDs - Part 2

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?


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: tdickinson 10 years ago
Orange Senior Belt
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.

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ