Re: QUESTION: How to update some records with one value, others with another

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 10 Sep 2009 08:39:38 -0700 (PDT)
Message-ID: <33f5ceab-582f-4e69-ba23-3abfe85ea4e0_at_k19g2000yqc.googlegroups.com>



On Sep 10, 8:49 am, ddf <orat..._at_msn.com> wrote:
> On Sep 10, 12:02 am, JJ <sa..._at_temporaryinbox.com> wrote:
>
>
>
>
>
> > Hi all,
>
> > I have a list of user records that I need to update once to solve a
> > problem with the application that uses the table.
>
> > The records in this table have two fields: name and company.
>
> > I need to update the company field with the existing company name in
> > order to get it to appear in the application using the DB.
>
> > Some of these have the company set to COMPANY X, others to COMPANY Y.
>
> > The question I needed to ask is how can I accomplish updating the
> > records once with the same company that they already have?
>
> > For example, would below SQL code work:
> > update user_records_table
> > set company=company
>
> Certainly it will, and you can even use functions to alter the case of
> the data if you like:
>
> SQL> create table upd_test(
>   2          name    varchar2(30),
>   3          company varchar2(40)
>   4  );
>
> Table created.
>
> SQL>
> SQL> insert all
>   2  into upd_test
>   3  values('Nord Flerper', 'COMPANY X')
>   4  into upd_test
>   5  values('Nerd Florper', 'COMPANY Y')
>   6  into upd_test
>   7  values('Nard Flurper', 'COMPANY Z')
>   8  into upd_test
>   9  values('Nurd Flarper', 'COMPANY X')
>  10  select * from dual;
>
> 4 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select name, company
>   2  from upd_test;
>
> NAME                           COMPANY
> ------------------------------ ------------------------------
> Nord Flerper                   COMPANY X
> Nerd Florper                   COMPANY Y
> Nard Flurper                   COMPANY Z
> Nurd Flarper                   COMPANY X
>
> SQL>
> SQL> update upd_test
>   2  set company=company;
>
> 4 rows updated.
>
> SQL>
> SQL> select name, company
>   2  from upd_test;
>
> NAME                           COMPANY
> ------------------------------ ------------------------------
> Nord Flerper                   COMPANY X
> Nerd Florper                   COMPANY Y
> Nard Flurper                   COMPANY Z
> Nurd Flarper                   COMPANY X
>
> SQL>
> SQL> update upd_test
>   2  set company=lower(company);
>
> 4 rows updated.
>
> SQL>
> SQL> select name, company
>   2  from upd_test;
>
> NAME                           COMPANY
> ------------------------------ ------------------------------
> Nord Flerper                   company x
> Nerd Florper                   company y
> Nard Flurper                   company z
> Nurd Flarper                   company x
>
> SQL>
> SQL> update upd_test
>   2  set company=upper(company);
>
> 4 rows updated.
>
> SQL>
> SQL> select name, company
>   2  from upd_test;
>
> NAME                           COMPANY
> ------------------------------ ------------------------------
> Nord Flerper                   COMPANY X
> Nerd Florper                   COMPANY Y
> Nard Flurper                   COMPANY Z
> Nurd Flarper                   COMPANY X
>
> SQL>
> SQL> update upd_test
>   2  set company=initcap(company);
>
> 4 rows updated.
>
> SQL>
> SQL> select name, company
>   2  from upd_test;
>
> NAME                           COMPANY
> ------------------------------ ------------------------------
> Nord Flerper                   Company X
> Nerd Florper                   Company Y
> Nard Flurper                   Company Z
> Nurd Flarper                   Company X
>
> SQL>
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

David, never nice example. I question why the OP needs to update the column to itself to get it to appear in the application. If the data exists it should appear though it may not appear in the desired format. Either it needs to be standardized into a common format where use of upper, lower, rtrim, and other character manipulation functions which your post covers or there is another issue the OP has not made clear.

In the case of standardization being required then here are some additional considerations. A series of updates may be necessary such as updating all company names to upper case or initcaps for every row in the table then updaing one variation of a comany name into another for those rows using a bad variation of the name. If the columns are unique keyed then eventually a delete or two may be necessary. This could require finding and comparing duplicate rows to determine which should be saved if additional non-shown columns exist before the data can be changed due to the duplicate key issue.

HTH -- Mark D Powell -- Received on Thu Sep 10 2009 - 10:39:38 CDT

Original text of this message