Delphix Products

Expand all | Collapse all

How to update the column data ? I am getting invalid sql statement in custom sql.

Jump to Best Answer
  • 1.  How to update the column data ? I am getting invalid sql statement in custom sql.

    Posted 03-04-2019 08:36:00 AM
    How to update the column data ? I am getting invalid sql statement in custom sql. I want to replace the existing data with my desired values.Can some one suggest please
    #Masking


  • 2.  RE: How to update the column data ? I am getting invalid sql statement in custom sql.
    Best Answer

    Posted 03-04-2019 08:46:00 PM
    Hi Rajesh,
    If you can share the sql queries you are making without sharing sensitive data, that would probably the best route here. If you cannot share the sql, you might want to open a ticket with our support engineers. 
    I see that you've been working with this for a while, based on your other questions: 
    https://community.delphix.com/delphix/topics/how-to-create-a-secure-lookup-algorithm-with-our-own-li...
    https://community.delphix.com/delphix/topics/is-there-any-way-masking-a-column-with-list-of-values-w...

    Thanks,
    Michael


  • 3.  RE: How to update the column data ? I am getting invalid sql statement in custom sql.

    Posted 03-05-2019 04:45:00 AM
    Hi Michael,


    For eg: I have a table EMPLOYEES with column EMAIL with below list of email id's in this EMAIL column:

    rajesh@delphix.com
    Michael@delphix.com
    hims@delphix.com
    marcin@delphix.com

    and want to replace "@" with "@test." as below:

    rajesh@test.delphix.com
    Michael@test.delphix.com
    hims@test.delphix.com
    marcin@test.delphix.com

    I am using below query to replace "@" with "@test." in custom sql.

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

    begin tran

    update EMPLOYEES set EMAIL = replace(EMAIL,'@','@tst.')


    But throwing error as invalid sql statement. Hope my query is clear now?


    Thanks,

    Rajesh



  • 4.  RE: How to update the column data ? I am getting invalid sql statement in custom sql.
    Best Answer

    Posted 03-05-2019 10:28:00 AM
    Hi Rajesh,

    In my understanding, custom SQL should be used for selection of data and not updating of data. If you want to update the data in table without using any of the Delphix algorithms, you can attach a post script to your masking job and put your update queries in that script.

    Any particular reason, why you want to update using Custom SQL ?

    Regards,
    Mayank


  • 5.  RE: How to update the column data ? I am getting invalid sql statement in custom sql.
    Best Answer

    Posted 03-05-2019 03:03:00 PM
    Hi Rajesh,

    In general cutom sql is used for filting out/in, subset or reformat data of a column befor masking it using an algorithm.

    In your case it will be more relevante to create a custom algorithm with you file, like detail in delhix doc "https://maskingdocs.delphix.com/Securing_Sensitive_Data/Configuring_Your_Own_Algorithms/
    " section "secure lookup framwork" and apply it to the email column this way will achive your goal.

    Option for a script based solution can be done also but from the virtualisation engine, by adding the sql script as hook for the VDB from the management intefrace.

    Regards,

    Mouhssine