Re: QUESTION: How to update some records with one value, others with another
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