Delete users from workday import based on location

[Community_User]

Originally posted on November 29 2021 at 21:24 UTC

Hello,

I'm using the user import preprocessor to try and remove users if their location is not equal to a set of defined locations however it seems to be removing all users from the temp users table. Below is my json.

{

"ADDITIONALTABLES": {

"USERS": "SELECT USERKEY, username,location,customer FROM USERS",

"TATTR": "SELECT attribute1,datasetname,attribute3 FROM DATASET_VALUES",

"CUSTOMER": "SELECT customerkey,customername FROM CUSTOMER"

},

"COMPUTEDCOLUMNS": [

"CUSTOMPROPERTY49",

"customer"

],

"PREPROCESSQUERIES": [

"DELETE FROM NEWUSERDATA WHERE NEWUSERDATA.location != '110' or NEWUSERDATA.location != '112' or NEWUSERDATA.location != '115' or NEWUSERDATA.location != '116' or NEWUSERDATA.location != '125' or NEWUSERDATA.location != '127' or NEWUSERDATA.location != '128' or NEWUSERDATA.location != '150' or NEWUSERDATA.location != '151' or NEWUSERDATA.location != '160' or NEWUSERDATA.location != '400' or NEWUSERDATA.location != '504' or NEWUSERDATA.location != '511' or NEWUSERDATA.location != '521'",

"UPDATE NEWUSERDATA SET CUSTOMPROPERTY49 = (select attribute3 from CURRENTTATTR where datasetname = 'Active_Directory_OUs' and attribute1 = NEWUSERDATA.location)",

"UPDATE NEWUSERDATA SET customer = (select currentcustomer.customername from currentcustomer where currentcustomer.customername = 'Bain')"

]

}

Below screenshot is showing the log where it seems to be updating and deleting all records.

Does anyone have any ideas?

Thanks,

Aundre

[Community_User]

Originally posted on November 29 2021 at 22:14 UTC

Your query is wrong. This would delete all the users

DELETE FROM NEWUSERDATA WHERE NEWUSERDATA.location != '110' or NEWUSERDATA.location != '112' or NEWUSERDATA.location != '115' or NEWUSERDATA.location != '116' or NEWUSERDATA.location != '125' or NEWUSERDATA.location != '127' or NEWUSERDATA.location != '128' or NEWUSERDATA.location != '150' or NEWUSERDATA.location != '151' or NEWUSERDATA.location != '160' or NEWUSERDATA.location != '400' or NEWUSERDATA.location != '504' or NEWUSERDATA.location != '511' or NEWUSERDATA.location != '521

[Community_User]

Originally posted on November 29 2021 at 22:17 UTC

What would be the correct query then? My understanding based on the query is that it should only delete users where the locations don't match any of the ones I mentioned.

[Community_User]

Originally posted on November 29 2021 at 22:21 UTC

try a not in clause 

[Community_User]

Originally posted on November 30 2021 at 13:33 UTC

Hi Nikhil,

Can you elaborate here. What should the query look like?

Thanks,

Aundre

[Community_User · Accepted solution]

Originally posted on November 30 2021 at 16:28 UTC

Hi,

You can try the below query :

DELETE FROM NEWUSERDATA WHERE NEWUSERDATA.location NOT IN ('110','112','115','116','125','127','128','150','151','160','400','504','511','521')

You might remove the single quotes if this does not work.

Regards,

Adrien COSSON