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

From: ddf <oratune_at_msn.com>
Date: Thu, 10 Sep 2009 09:46:31 -0700 (PDT)
Message-ID: <c63d9a09-7a70-4ed0-b8be-6814597311e0_at_w36g2000yqm.googlegroups.com>



On Sep 10, 10: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 -

Some applications behave in strange and weird ways; the update may fire a trigger which populates a related table the application uses to return display results, for example:

SQL> create table upd_test(

  2          form    number,
  3          name    varchar2(30),
  4          company varchar2(40)

  5 );

Table created.

SQL>
SQL> insert all
  2 into upd_test
  3 values(1, 'Nord Flerper', 'COMPANY X')   4 into upd_test
  5 values(2, 'Nerd Florper', 'COMPANY Y')   6 into upd_test
  7 values(3, 'Nard Flurper', 'COMPANY Z')   8 into upd_test
  9 values(4, 'Nurd Flarper', 'COMPANY X')  10 select * from dual;

4 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create table display_vals(

  2          form    number,
  3          disp_val varchar2(40)

  4 );

Table created.

SQL>
SQL> insert into display_vals
  2 (form)
  3 select form from upd_test;

4 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create or replace trigger upd_disp_vals_trg   2 before insert or update on upd_test   3 for each row
  4 begin

  5          if inserting then
  6                  insert into display_vals
  7                  values(:new.form, :new.company);
  8          elsif updating then
  9                  update display_vals
 10                  set disp_val = :new.company
 11                  where form = :new.form;
 12          end if;

 13
 14 end;
 15 /

Trigger created.

SQL>
SQL> --
SQL> -- Production data
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> --
SQL> -- Display data
SQL> --
SQL> -- for now there is nothing to display
SQL> -- on the GUI

SQL> --
SQL> select form, disp_val
  2 from display_vals;

      FORM DISP_VAL

---------- ----------------------------------------
         1
         2
         3
         4

SQL>
SQL> --
SQL> -- Make the 'useless' update

SQL> --
SQL> update upd_test
  2 set company=company;

4 rows updated.

SQL>
SQL> --
SQL> -- Display values now available
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> select form, disp_val
  2 from display_vals;

      FORM DISP_VAL

---------- ----------------------------------------
         1 COMPANY X
         2 COMPANY Y
         3 COMPANY Z
         4 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> select form, disp_val
  2 from display_vals;

      FORM DISP_VAL

---------- ----------------------------------------
         1 company x
         2 company y
         3 company z
         4 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> select form, disp_val
  2 from display_vals;

      FORM DISP_VAL

---------- ----------------------------------------
         1 COMPANY X
         2 COMPANY Y
         3 COMPANY Z
         4 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>
SQL> select form, disp_val
  2 from display_vals;

      FORM DISP_VAL

---------- ----------------------------------------
         1 Company X
         2 Company Y
         3 Company Z
         4 Company X

SQL> I can't think of a good reason why this type of design would be implemented, but it's a remote possibility.

David Fitzjarrell Received on Thu Sep 10 2009 - 11:46:31 CDT

Original text of this message