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

From: ddf <oratune_at_msn.com>
Date: Thu, 10 Sep 2009 05:49:14 -0700 (PDT)
Message-ID: <b5aadb80-7e7b-4939-86a0-ae47ac309d0f_at_r39g2000yqm.googlegroups.com>

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 Received on Thu Sep 10 2009 - 07:49:14 CDT