Custom Query Job failing

[Khushboo]

Hi,

The below custom query job is failing. Please let me know if anything is is wrong 

UPDATE Users U, USER_ACCOUNTS UA, ENDPOINTS E JOIN  USER_ACCOUNTS UA ON U.USERKEY=UA.USERKEY AND ACCOUNTS A ON UA.ACCOUNTKEY = A.ACCOUNTKEY JOIN ENPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY SET U.STATUSKEY = 0 where E.ENDPOINTNAME = 'HCConnect-SNOW' AND UPPER(A.STATUS) = 'Manually Provisioned'

[Manu269]

Do you see any error?

[Khushboo]

Below is the error :

 DEBUG services.SaviyntCommonUtilityService - Error Occured in customQueryExecution - true and exception - java.sql.BatchUpdateException: 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 'A ON UA.ACCOUNTKEY = A.ACCOUNTKEY JOIN ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINT' at line 1\n","stream":"stdout","time":"2023-11-10T11:31:48.848603056Z"}

[DixshantValecha]

It looks like there are some syntax errors in your SQL query. Here is the corrected version:

sql
Copy code
UPDATE Users U
JOIN USER_ACCOUNTS UA ON U.USERKEY = UA.USERKEY
JOIN ACCOUNTS A ON UA.ACCOUNTKEY = A.ACCOUNTKEY
JOIN ENDPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY
SET U.STATUSKEY = 0
WHERE E.ENDPOINTNAME = 'HCConnect-SNOW' AND UPPER(A.STATUS) = 'MANUALLY PROVISIONED';
Here are the changes made:

Removed the duplicate JOIN USER_ACCOUNTS UA ON U.USERKEY = UA.USERKEY since it's already included in the UPDATE statement.

Fixed the join syntax to use JOIN instead of , before USER_ACCOUNTS UA.

Corrected the case of SQL keywords (JOIN, UPDATE, SET, WHERE, AND, UPPER).

Make sure to test this query in a safe environment before running it in a production setting to avoid unintended consequences. If you are still facing issues, please provide  specific error message you are encountering.

[amit_krishnajit]

The issue seems to be with the SQL query and not a Saviynt issue. You may want to update your query using joins.

UPDATE Users U
JOIN USER_ACCOUNTS UA ON U.USERKEY=UA.USERKEY
JOIN ACCOUNTS A ON UA.ACCOUNTKEY = A.ACCOUNTKEY 
JOIN ENPOINTS E ON A.ENDPOINTKEY = E.ENDPOINTKEY 
SET U.STATUSKEY = 0 
WHERE E.ENDPOINTNAME = 'HCConnect-SNOW' AND UPPER(A.STATUS) = 'Manually Provisioned';

Note- Recommend you to not use Custom Query jobs and look for alternative approaches like Saviynt for Saviynt or updating users via API.