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

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • Answered
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
Photo of Rajesh Dadi

Rajesh Dadi

  • 402 Points 250 badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of MichaelT

MichaelT, Official Rep

  • 1,992 Points 1k badge 2x thumb
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
Photo of Rajesh Dadi

Rajesh Dadi

  • 402 Points 250 badge 2x thumb
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

Photo of Mayank Ahluwalia

Mayank Ahluwalia

  • 646 Points 500 badge 2x thumb
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
Photo of Mouhssine SAIDI

Mouhssine SAIDI

  • 5,846 Points 5k badge 2x thumb
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