CODEWITHSUNDEEP
oracle-databasenestedsql-updatecase
DanSask
DanSask

Reputation: 27

UPDATE NESTED CASE

I am using TOAD which connects to Oracle 11g.

I am working on parsing an address field, which will contain building number, street name, street type, direction, sub-unit, and Civic Number Suffix Type.

Because of the complexity in how address's are allowed to be entered and still be valid (or just having incorrect information which Im ignoring for now) I am having to do some very specific regular expressions to break apart the building number, sub-unit, and Civic Number Suffix Type.

I have used the case statement before where a single field is being updated

UPDATE TEMP_PARSE_EXIST
SET ADT_ACT =
CASE
    WHEN REGEXP_LIKE(ADRS, 'P\.?\s?O\.?\s+BOX', 'i') THEN 'PO BOX'
    WHEN REGEXP_LIKE(ADRS,'(\s|^)(RR|GD)(\s|$)', 'i') THEN 'QUERY ERROR: RR OR GD'
    ELSE NULL
END
WHERE ADT_ACT IS NULL;

But what I need now is different as the field I will be updating will depend on the case and it keeps giving me a missing set keyword error.

UPDATE TEMP_PARSE_EXIST
CASE
    WHEN REGEXP_LIKE(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+\s\w+', 'i'), '(ABBEY|ACRES|ALLÉE|ALLEY|AUT|AUTOROUTE|AV|AVE|AVENUE|BAY|BEACH|BEND|BLVD|BOUL|BOULEVARD|BYPASS|WYND)$', 'i') THEN 
        CASE
            WHEN REGEXP_LIKE(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '^\w+(\s?-\s?)\d+$') THEN SET ADT_ACT = 'CASE 1', V_NUM = REGEXP_SUBSTR(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '\d+$')
            WHEN REGEXP_LIKE(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '^\w+(\s?-\s?)\d+(ST|ND|RD|TH)$') THEN SET ADT_ACT = 'CASE 2', V_STREET_NAME = REGEXP_SUBSTR(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '\d+(ST|ND|RD|TH)$')
            ELSE SET ADT_ACT = 'FIRST ERROR SPOT'
        END
END
WHERE ADT_ACT IS NULL;

This is just the start of the many cases which will parse these pieces of information apart. So there will be more of them to follow, and the nested case statement is useful. Yes this could be done with a regular case statement, but the lines would be longer and there would be repetition of work.

The sources I find on nested case statements are always for select statements or in plsql and I need it for the UPDATE. https://community.oracle.com/thread/1094856?start=0&tstart=0

Upvotes: 0

Views: 855

Answers (2)

MT0
MT0

Reputation: 168440

The syntax for the UPDATE statement is:

UPDATE statement syntax

Update_set_clause ::=

UPDATE set clause

You cannot do:

UPDATE table_name
CASE WHEN ... THEN SET ...
     WHEN ... THEN SET ...

It will probably be easier to split it up into multiple statements:

UPDATE TEMP_PARSE_EXIST
SET ADT_ACT = 'CASE 1',
    V_NUM   = REGEXP_SUBSTR(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '\d+$')
WHERE ADT_ACT IS NULL
AND   REGEXP_LIKE(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+\s\w+', 'i'), '(ABBEY|ACRES|ALLÉE|ALLEY|AUT|AUTOROUTE|AV|AVE|AVENUE|BAY|BEACH|BEND|BLVD|BOUL|BOULEVARD|BYPASS|WYND)$', 'i')
AND   REGEXP_LIKE(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '^\w+(\s?-\s?)\d+$');

UPDATE TEMP_PARSE_EXIST
SET ADT_ACT = 'CASE 2',
    V_STREET_NAME = REGEXP_SUBSTR(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '\d+(ST|ND|RD|TH)$')
WHERE ADT_ACT IS NULL
AND   REGEXP_LIKE(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+\s\w+', 'i'), '(ABBEY|ACRES|ALLÉE|ALLEY|AUT|AUTOROUTE|AV|AVE|AVENUE|BAY|BEACH|BEND|BLVD|BOUL|BOULEVARD|BYPASS|WYND)$', 'i')
AND   REGEXP_LIKE(REGEXP_SUBSTR(ADRS, '^\w+(\s?-\s?)\w+'), '^\w+(\s?-\s?)\d+(ST|ND|RD|TH)$');

...

Update:

The regular expressions can be made simpler (or at least reduce the duplication and nesting of the expressions):

UPDATE TEMP_PARSE_EXIST
SET ADT_ACT = 'CASE 2',
    V_STREET_NAME = REGEXP_SUBSTR( ADRS, '^\w+(\s?-\s?)(\d+(ST|ND|RD|TH))', 1, 1, 'i', 2 )
WHERE ADT_ACT IS NULL
AND   REGEXP_LIKE( '^\w+(\s?-\s?)\d+(ST|ND|RD|TH)\s\w*(ABBEY|ACRES|ALLÉE|ALLEY|AUT|AUTOROUTE|AV|AVE|AVENUE|BAY|BEACH|BEND|BLVD|BOUL|BOULEVARD|BYPASS|WYND)', 'i' );

Upvotes: 1

spencer7593
spencer7593

Reputation: 108490

The SET keyword and assignment is invalid in the CASE expression.

Following the THEN and ELSE keywords must be an expression that evaluates to a value. The expression returned can be another CASE expression, it is possible to nest CASE expressions.

It is not possible to include the SET keyword for the UPDATE within the CASE expression.

If you want to update a column, the first form is correct... 

 UPDATE mytable
    SET col = expr
  WHERE ...

The expression can be a "nested CASE". If there's a condition where you don't want to update the column, then return the current value of the column 

 UPDATE mytable
    SET mycol = CASE
                  WHEN foo = bar 
                    THEN CASE
                           WHEN fee > 0
                             THEN ...
                           WHEN fi = 'abc'
                             THEN ...
                           ELSE mycol
                         END
                  ELSE mycol
                END
  WHERE ...

Upvotes: 0

Related Questions