DB2 SQL statemnet in AIX to update the confidential data to TST1, TST2, ... TSTn which n can be row number

Posted on 2014-08-08
Last Modified: 2014-08-16
I need a AIX db2 sql statement to change all the data of customer column/field to TST1, TST2, ... TSTn which n can be row number to prevent duplicate data. Many thanks.
Question by:martinlau
    LVL 47

    Expert Comment

    would you like to provide:

    the table name?
    the field name?
    is there any order you want imposed on the integer? (what fields are used for that order?)
    is there an existing integer we could use for this? what is it's name?

    & anything else you think you might have missed in the very brief question
    LVL 37

    Accepted Solution

    try something like this

    update my_table t1
    set t1.customer_column = (select rown
                                                  from (select row_number() over(order by customer_id) as rown, customer_id
                                                             from my_table) t2
                                                   where customer_id = t1.customer_id)

    Author Comment

    Thanks a lot for your response. I found out that my question is mislead and not correct and would like to revise as follows:

    For example, I have 2 tables customer1, customer2 and both of them have customerid, customer name,phone data field, now I would like to change the customer name of both tables to mask the confidential information by changing the customer name to test1, test2 which test is the masking fixed string and n is any unique number. This moment, as I could not confirm the rows of two tables is exact same, I also need to ensure the matching between them. For example, customerid 12345 with new customer name test999 in table customer1 and we should also have customerid 12345 record with customer name test999 in table customer2. please do me a flavor to provide further advice accordingly? Thx.
    LVL 47

    Expert Comment

    At the very least you should provide the table definitions - you really are making this hard - for you as well as us.

    Without simple facts like table names and fields names we will "invent" some, you will then need to "translate" those - and not make mistakes in the translation.

    Believe me, it is easier for everyone involved if you tell us the table names and the fields names.

    Author Comment

    Thanks for all experts which had given their advice to me before.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now