更新嵌套案例 [英] UPDATE NESTED CASE

问题描述

我正在使用连接到Oracle 11g的TOAD.

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.

在更新单个字段之前，我已经使用过case语句

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;

但是我现在需要的是不同的，因为我要更新的字段将取决于大小写，并且会不断给我一个缺少set关键字的错误.

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;

这只是将这些信息分开解析的许多情况的开始.因此，将会有更多的内容可以遵循，并且嵌套的case语句很有用.是的，这可以通过常规的案例说明来完成，但是排队的时间会更长，并且会重复工作.

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.

我在嵌套case语句中找到的源始终用于select语句或plsql中，而更新则需要它. https://community.oracle.com/thread/1094856?start=0& ; tstart = 0

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

推荐答案

UPDATE语句为:

Update_set_clause :: =

Update_set_clause ::=

您不能这样做:

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)$');

...

更新:

可以使正则表达式更简单(或至少减少表达式的重复和嵌套):

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' );

这篇关于更新嵌套案例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！