This discussion is archived
2 Replies Latest reply: Dec 7, 2012 8:17 AM by LPS RSS

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

978649 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Update like this...

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points