CODEWITHSUNDEEP
sqloracle-database
KS1
KS1

Reputation: 1039

Oracle UPDATE remove prefixed value from column data

I have the following query that prefixes a p_id with a p_number

UPDATE table1 t1 
   SET t1.p_id = CONCAT((SELECT CONCAT(t2.p_number,' ') 
                           FROM tble2 t2 
                          WHERE t2.id = t1.p_id)
                        , t1.p_id) 
 WHERE t1.p_id = '19680';

Which basically takes values like 

t2.p_number:X12
t1.p_id:    19680

and turns it into 

t2.p_number:X12
t1.p_id:    X12 19680

What I have been asked for is a 'rollback' script, baring in mind the only column I will know the value of is the '19680'! I have looked at the REPLACE command but not sure if I can get it to work; any ideas?

Resolved!

Thanks guys, a log of good tips there, I have gone for what in the end looks a quite simple query from Ben.

There's quite a bit of history behind this issue, where basically a client has entered the values for p_id and has entered duplicates. They therefore want to prefix the p_number onto the p_id to make them unique again (once entered the client can not update), but they also want a get-out-clause, in case something goes wrong so they want to be able to remove the prefixed data.

The p_id is actually used within the system so virtual tables and code changes were not really feasible in this scenario, rather it was easiest to just update the client's data for them.

Cheers guys, hope the client is happy with the proposal.

Upvotes: 0

Views: 2376

Answers (4)

Colin 't Hart
Colin 't Hart

Reputation: 7729

Which version of Oracle are you using? If Oracle 11g you could use virtual columns for this!

alter table table1
add new_column as p_number || ' ' || p_id;

I'm assuming here that all of your columns are of type varchar.

With this approach you will have to update client code to reference the new column.

Upvotes: 0

Ben
Ben

Reputation: 52863

Here's my 2c.

Do not update your original field. If there's a possibility that you want to roll it back then you cause yourself massive problems. Create a new field that you update with the new values. When you want to reference this field you can do so. When you want to reference another field you can do that.

More generally, this is a good tactic for any fields that you want to "fix". You may improve what you're "fixing" later on and if you've overwritten the original data you're unable to. Always keep the raw data and then you can re-use it as often as you like.

If all you want to do is remove everything before the first space then the following will work:

update the_table
   set p_id = substr(p_id, instr(p_id, ' ') + 1);

SQL Fiddle

Upvotes: 1

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

As a general rule, do not store the data you can infer.

Just prefix the field in the client code, or in a VIEW, or a virtual column (Oracle 11), without modifying the original value. Since you haven't modified it, there is nothing to rollback.

Upvotes: 1

IndoKnight
IndoKnight

Reputation: 1864

If you are asking how to do this simply, then trim away the prefix till the space and get back your original value. If you are looking at a better option, then I'd say to create another column like "old_p_id" and then push the old one into it. If you want to rollback, you can always update p_id with old_p_id.

+----------------------+
¦ p_id      ¦ old_p_id ¦
¦-----------+----------¦
¦ X12 19680 ¦  19680   ¦
¦ X13 19681 ¦  19681   ¦
+----------------------+

Upvotes: 0

Related Questions