How to Modify records in Authorization table with Script / automated task

[Jothi]

The permissions related to a user can change now and then in the source system.  I am planning to run an automated scheduled task each day, that will bring incremental updates to data. Below is my section access script. 

After the daily update is run, I want to check Table T1 and if it has the BUNITIDs then the BUNIT in Authorization table shall stay, else the BUNITIDs that are not present in T1 must have the corresponding entry in Authorization table removed. Is there any script/syntax, that I can use to check T1 and remove rows from the Authorization table? 

Section Access;
Authorization:
LOAD * inline [
ACCESS, USERID, BUNITID
ADMIN, SAML\u1, *
USER, SAML\u2, 2706
USER, SAML\u7, 2712
USER, SAML\u9, 2770
USER, SAML\u11, 2776
];
Section Application;

[pedrobergo]

Hi @Jothi ,

If i understood your requirements, you have a standard BUNITID access, and during a loading script you may have to compare with another table, and if some user has anothers access it will be used.

You must to create a AUTHORIZATION table using T1 before the standard AUTH, then you can use EXISTS clause to check this and create the missing lines. Remeber that you can use a FROM clause inside Section Access populating it directly from your source table;

See this code sample:

Section Access;

// T1 Table can be loaded as you need, just calling a AUTHORIZATION.
Authorization:
LOAD *
INLINE [
ACCESS, USERID, BUNITID
USER, SAML\u2, 3706
USER, SAML\u7, 3712
];

// Standard authorization table use a NOT EXISTS clause
Authorizathion:
LOAD *
INLINE [
ACCESS, USERID, BUNITID
ADMIN, SAML\u1, *
USER, SAML\u2, 2706
USER, SAML\u7, 2712
USER, SAML\u9, 2770
USER, SAML\u11, 2776
]
WHERE NOT EXISTS(USERID);

Section Application;