Systemusername Uniquness Check in preprocessor

[IGAQ]

Hello All,

We are generating unique systemusername  in preprocessor where we need to connect to accounts table ( for AD Accounts), using below query (not even added Accounts table , just checking only users table)- but its throwing an error  "java.sql.SQLException: You can't specify target table 'NU' for update in FROM clause" - Is it possible like below ?any inputs/alternatives on this ?

=================

"UPDATE NEWUSERDATA NU INNER JOIN CURRENTUSERS CU ON NU.USERNAME = NU.USERNAME SET NU.SYSTEMUSERNAME = CASE WHEN ( (SELECT CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) ,MAX(CAST(REPLACE(REVERSE(CAST( REVERSE(NU.SYSTEMUSERNAME) AS SIGNED) )+1,'.0','') AS UNSIGNED))) FROM CURRENTUSERS CU WHERE NU.USERNAME LIKE 'EMPC%' AND NU.SYSTEMUSERNAME IS NOT NULL AND NU.SYSTEMUSERNAME LIKE CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME),'%')) !='' ) THEN(SELECT CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) ,MAX(CAST(REPLACE(REVERSE(CAST( REVERSE(NU.SYSTEMUSERNAME) AS SIGNED) )+1,'.0','') AS UNSIGNED))) FROM NEWUSERDATA CU WHERE NU.USERNAME LIKE 'XYZ%' AND NU.SYSTEMUSERNAME IS NOT NULL AND NU.SYSTEMUSERNAME LIKE CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME),'%')) ELSE CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) END LIMIT 1",

[sudeshjaiswal]

Hello @IGAQ,

The join condition in the query is incorrect.You are using NU.USERNAME = NU.USERNAME, It should be NU.USERNAME = CU.USERNAME.

"UPDATE NEWUSERDATA NU 
INNER JOIN CURRENTUSERS CU ON NU.USERNAME = CU.USERNAME 
SET NU.SYSTEMUSERNAME = CASE 
    WHEN ( 
        (SELECT CONCAT(
            CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) ,
            MAX(CAST(REPLACE(REVERSE(CAST( REVERSE(NU.SYSTEMUSERNAME) AS SIGNED) )+1,'.0','') AS UNSIGNED))
        ) 
        FROM CURRENTUSERS CU 
        WHERE NU.USERNAME LIKE 'EMPC%' AND NU.SYSTEMUSERNAME IS NOT NULL AND NU.SYSTEMUSERNAME LIKE CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME),'%')) !='' 
    ) THEN(
        SELECT CONCAT(
            CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) ,
            MAX(CAST(REPLACE(REVERSE(CAST( REVERSE(NU.SYSTEMUSERNAME) AS SIGNED) )+1,'.0','') AS UNSIGNED))
        ) 
        FROM NEWUSERDATA CU 
        WHERE NU.USERNAME LIKE 'XYZ%' AND NU.SYSTEMUSERNAME IS NOT NULL AND NU.SYSTEMUSERNAME LIKE CONCAT(CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME),'%')
    ) ELSE CONCAT(SUBSTRING(IFNULL(NU.PREFEREDFIRSTNAME,NU.FIRSTNAME),1,1),NU.LASTNAME) 
END LIMIT 1",

Thanks

[nvachhani]

Does this check uniqueness of systemusername against existing users?

[rushikeshvartak]

systemusername does not allowed duplicate hence its checked 

[nvachhani]

Does it check against the systemusername generation rule or does uniqueness conditions need to be added to inline preprocessor queries? 

[rushikeshvartak]

Its automatically taken care internally in database