title: Oracle UPDATE remove prefixed value from column data
body: <p>I have the following query that prefixes a p_id with a p_number</p>
  
  <pre><code>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';
  </code></pre>
  
  <p>Which basically takes values like </p>
  
  <pre><code>t2.p_number:X12
  t1.p_id:    19680
  </code></pre>
  
  <p>and turns it into </p>
  
  <pre><code>t2.p_number:X12
  t1.p_id:    X12 19680
  </code></pre>
  
  <p>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?</p>
  
  <hr>
  
  <p>Resolved!</p>
  
  <p>Thanks guys, a log of good tips there, I have gone for what in the end looks a quite simple query from Ben.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Cheers guys, hope the client is happy with the proposal.</p>