CODEWITHSUNDEEP
sqloracle-databaseoracle11gsql-update
D. Haskins
D. Haskins

Reputation: 121

Replace table column value using part of same column

I am updating a column in my table, replacing a column with part of its existing value:

Update Table_Name Set VarName '12345' where VarName = 'AA-BB-12345';
Update Table_Name Set VarName '99999' where VarName = 'XX-AR-99999';
...

How to do this in a loop so it makes changes through entire Table_Name table pulling out the last element of VarName and using that to update? There are 500 rows to be updated in the table, too many to do single update lines, so I need a loop to accomplish this.

I think this is close:

Update Table_Name
set VarName = ( select regexp_substr(VarName, '[^-]+,1,3 ) from Table_Name );

Upvotes: 0

Views: 157

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You aren't that far off, but your subquery isn't correlated - there is no link between the value(s) the subquery finds and the row being updated, so you're probably getting an ORA-01427 error. But you don't need the subquery since you're updating the same row:

update table_name set varname = regexp_substr(varname, '[^-]+', 1, 3);

You can do it without a regular expression too:

update table_name set varname = substr(varname, instr(varname, '-', -1) + 1);

which also has the advantage of not nulling any rows which aren't the expected pattern. Even so, you could reduce unnecessary work by only updating values with dashes:

update table_name set varname = substr(varname, instr(varname, '-', -1) + 1)
where instr(varname, '-', -1) > 0;

or

update table_name set varname = regexp_substr(varname, '[^-]+', 1, 3)
where regexp_substr(varname, '[^-]+', 1, 3) is not null;

which will only update rows where the old value has (at least) three elements.

The two updates will behave differently if there are two elements - the first one with substr/instr will always take the last part after a dash if there are any; The second one with rexexp_substr will not update one with a single dash. You've only shown one fixed pattern so none of that may be relevant to you. At least, until you run the same update again against that data...

Upvotes: 2

Related Questions