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

On Sep 10, 12:02*am, JJ <sa... (AT) temporaryinbox (DOT) com> wrote:
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

On Sep 10, 8:49*am, ddf <orat... (AT) msn (DOT) com> wrote:
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 --

On Sep 10, 11:39*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
The "never" is a typo. I proof read right over that.

mark

On Sep 10, 10:39*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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

On Sep 9, 10:02*pm, JJ <sa... (AT) temporaryinbox (DOT) com> wrote:
Would you happen to know why it doesn't appear in the app? Did you
disable an index or something?

jg
--
@home.com is bogus.
http://favstar.fm/users/_why