Sql Where Expressions

Goal

Use the where keyword when you need filtering of rows for partial table anonymizations or database subsetting.

Custom SQL notes

• There will be no check of SQL syntax
• Any occurrences of double quotes or backslash inside a SQL statement must be escaped with a backslash: \" and \.
• A single statement cannot be divided on serveral lines. Multiple SQL statements may be written divided by newline

Use cases

A where clause can be used

• to filter rows in update, so the anonymizations are done on specific parts of the table data
• to filter rows in delete for subsetting the data

Ano Structure and Example

Structure

    update <table1>
        where "..."
        <table1-anonymizations>

    delete <table2>
        where "..." {
        <table2-deletions>
        }

Update Example

    update Address
        where "Address.customer_customerNo NOT IN (SELECT DISTINCT Customer.customerNo FROM ano_hotel.Customer)"
        mask customer_customerNo mask_customer_customerNo_leftover_from_propagate
            format %d
            unique
            sequence 100 1
            temporary-value 999999

Delete Example

    delete HOTELCHAIN
        where "ID = 0" {
        cascade HOTEL parent ID child CHAIN_ID {
            cascade BOOKING parent ID child HOTEL_ID {
                cascade STAY parent ID child BOOKING_ID {
                    cascade NIGHT parent SERIALNO, CUSTOMER_CUSTOMERNO
                    child STAY_SERIALNO, STAY_CUSTOMER_CUSTOMERNO
                }
            }
        }
    }