2 Replies Latest reply: Dec 7, 2012 10:17 AM by LPS RSS

    Update Table from "<text> to "DE<text>"

    978649
      Dear experts,

      I'd like to know how to update a value in my DB as mentioned in the subject. For example:

      I have a row "PLZ" where the postal codes are in, now I want them to change as follows:

      Old value: 12345
      New value: DE12345

      Is there any possibility to do this with an update statement? I have many records in this row "PLZ", so I'd like to have one update statement to change them all!


      Thanks in advance!
        • 1. Re: Update Table from "<text> to "DE<text>"
          Frank Kulash
          Hi,

          Welcome to the forum!
          975646 wrote:
          Dear experts,

          I'd like to know how to update a value in my DB as mentioned in the subject. For example:

          I have a row "PLZ" where the postal codes are in, now I want them to change as follows:
          Do you mean you have a column called PLZ?
          Old value: 12345
          New value: DE12345

          Is there any possibility to do this with an update statement?
          Sure. Assuming PLZ is a VARCHAR2 column, and that there is room for 2 more characters:
          UPDATE  table_x
          SET     plz   = 'DE' || plz
          WHERE   plz   IS NOT NULL     -- If needed
          ;
          I'm just guessing you'll want the WHERE clause above. Without it, a row where plz was NULL would be changed to 'DE'. Including the WHERE clause above leaves those rows with NULL plz.


           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          If you're asking about a DML statement, such as UPDATE, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: Update Table from "<text> to "DE<text>"
            LPS
            Update like this...

            UPDATE table_x
            SET plz = 'DE' || plz
            WHERE plz =12345