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:40:47 -0700 (PDT)
Message-ID: <a6884aa4-baf1-43d0-9255-682738a1c859_at_r39g2000yqm.googlegroups.com>



On Sep 10, 11:39 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -

The "never" is a typo. I proof read right over that.

mark Received on Thu Sep 10 2009 - 10:40:47 CDT

Original text of this message