CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Carlos R
Carlos R

Reputation: 51

Using Right() or Substr() in an UPDATE statement

I have two tables :

Add_T
Add_ID   NUMBER(10,0)       
Add_GEOMETRY    SDO_GEOMETRY            
STRTN    VARCHAR2(40 BYTE)       
CITY      VARCHAR2(45 BYTE)         
STATE     VARCHAR2(2 BYTE)      
ZIPCODE   VARCHAR2(10 BYTE)     
HC_ID   NUMBER(10,0)        
P_ID    NUMBER(10,0)

Second Table :

HC_T
HC_GEOMETRY    SDO_GEOMETRY 
HC_ID     NUMBER(10,0)
TYPE      VARCHAR2(2 BYTE)
FACY     NUMBER(10,0)
COORD_X   NUMBER(15,0)
COORD_Y     NUMBER(15,0)

I need to update the field HC_ID of Add_T Table and I use the following SQL statement:

 update add_t set hc_Id = ( SELECT HC_ID FROM HC_T
 WHERE ADD_T.P_ID = HC_T.FACY  AND
 HCO_T.TYPE='R' ) WHERE hc_ID IS NULL and
 subtr(strtn,-4,4) = "-LOC"

It doesn't work. Also, I used the right() function and also got incorrect results.

 update add_t set hc_Id = ( SELECT HC_ID FROM HC_T
 WHERE ADD_T.P_ID = HC_T.FACY  AND
 HCO_T.TYPE='R' ) WHERE hc_ID IS NULL and
 RIGHT(strtn,4) = "-LOC"

Could anyone say where is the mistake?

Upvotes: 0

Views: 35

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Looks like you need the last 4 characters of a string. If so, then you'd

UPDATE add_t
   SET hc_id =
          (SELECT hc_id
             FROM hc_t
            WHERE     add_t.p_id = hc_t.facy
                  AND hco_t.TYPE = 'R')
 WHERE     hc_id IS NULL
       AND SUBSTR (strtn, -4) = '-LOC';

because e.g.

SQL> SELECT SUBSTR ('some string', -4) FROM DUAL;

SUBS
----
ring

SQL>

(Note also that you'd enclose strings into single quotes in Oracle, not double ones (as you tried with the "-LOC" string).

Upvotes: 1

Related Questions