CODEWITHSUNDEEP
sqlregexoracle-databasesql-update
GLaDOS
GLaDOS

Reputation: 672

Oracle sql update using regexp_replace

I have almost 2000 rows in a table ("Sensor", which has many more than 2000 rows) in which I need to update one column, the sensorname.

Part of the replacement within the update is based on the contents of another table, deviceport.

Deviceport is related to the updating table through this deviceportid column -- sensor.deviceportid = deviceportid. Thus the actual update is different for every row. I don't want to have to write 2000 update statements, but I haven't been able to figure out what my "where" statement will say.

UPDATE sensor sn SET sn.sensorname = ( 
    SELECT REGEXP_REPLACE(
        sensorname, 
        '^P(\d)', 
        'J ' || (
                SELECT d.deviceportlabel 
                FROM deviceport d 
                WHERE d.deviceportid = s.deviceportid
                ) || 
       ' Breaker \1'
    )
    FROM sensor s 
    WHERE REGEXP_LIKE( sensorname, '^P(\d)') 
) 
WHERE ...?

Any clues?

Upvotes: 0

Views: 6732

Answers (2)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23727

UPDATE sensor sn 
SET sn.sensorname = 'J ' || 
   (
     SELECT d.deviceportlabel 
     FROM deviceport d 
     WHERE d.deviceportid = sn.deviceportid
   ) || ' Breaker ' || substr(sn.sensorname, 2, 1)
WHERE REGEXP_LIKE( sn.sensorname, '^P\d')

Upvotes: 1

chetan
chetan

Reputation: 2886

Try this:- 

UPDATE sensor sn, deviceport d 
SET sn.sensorname = REGEXP_REPLACE(
        sn.sensorname,'^P(\d)', 'J '|| d.deviceportlabel||' Breaker \1')
WHERE d.deviceportid = sn.deviceportid
AND REGEXP_LIKE( sn.sensorname, '^P(\d)');

Upvotes: 2

Related Questions