CUSTOMQUERYJOB Query

[Rayomand]

Hello,
I am trying to run this query once but the job keeps failing and I cant see any error , not sure what the issue is

update users set username = SUBSTRING_INDEX(SUBSTRING_INDEX(username, '_', -3), '_', 1),
email = SUBSTRING_INDEX(SUBSTRING_INDEX(email, '_', -3), '_', 1) where
statuskey = 0 and
employeetype = 'Guest' and
email like '%_DeActivated' and
SUBSTRING_INDEX(SUBSTRING_INDEX(email, '_', -3), '_', 1) not in (select
email
from users
where
statuskey = 1 and
employeetype = 'Guest' and
email not like '%_DeActivated')
 

[Dhruv_S]

Hi @Rayomand 

Thanks for reaching out to Saviynt forums.

Could you please confirm where you are using the above query. I can see the query updates few things on users with employee type "Guest" and email containing string "_DeActivated". Are there any users in your environment which satisfies these conditions. This seems to be a sample query which need to be modified as per the requirements.

Regards,
Dhruv Sharma
If this reply answered your question, please accept it as Solution to help others who may have a similar problem.

[Rayomand]

Hello,

The query is meant to update the username and email id of some guest users.       
I am using it in the custom query job in my testing environment.
There  are many users that exist with the conditions specified.
Its not a sample query but the exact one that I want to execute but it keeps failing and I am not sure why?

  

[Dhruv_S]

Please check the logs and also validate the users satisfying select query in data analyzer.

select * from users where
statuskey = 0 and
employeetype = 'Guest' and
email like '%_DeActivated' and
SUBSTRING_INDEX(SUBSTRING_INDEX(email, '_', -3), '_', 1) not in (select
email
from users
where
statuskey = 1 and
employeetype = 'Guest' and
email not like '%_DeActivated'

Regards,

Dhruv Sharma

[Rayomand]

I ran the select before the update its working fine,  the query u have used in ur reply is missing the inner query which is a crucial part,
I am unable to find anything in the logs

[rushikeshvartak · Accepted solution]

You can’t have same table in update and where clause. You extract username where you want to perform change and add into condition 

update users set username = SUBSTRING_INDEX(SUBSTRING_INDEX(username, '_', -3), '_', 1),
email = SUBSTRING_INDEX(SUBSTRING_INDEX(email, '_', -3), '_', 1) where
statuskey = 0 and
employeetype = 'Guest' and
email like '%_DeActivated' and username in ()

[Rayomand]

Hello,
      Is there an issue with  "not in" sql condition ?
as I tried to execute a query with it and it failed, as a test I just put "in" and it worked, if this is an issue I will have to re-write the query as the number of users for the condition that works using "in"  is  huge.

[rushikeshvartak]

There is no issue in not in