On Sep 10, 9:28 am, Javier Montani <[email protected]> wrote:
> Yes, but what's the point of doing: update user_records_table set
> company=company ??
> If you are doing an update is because you need to modify something.
> JJ, can you post an example of what you need?
>
> 2009/9/10 Michael Moore <[email protected]>
>
>
>
> > It sounded to me like he has only one table. I don't know how updating the
> > company name with the value that is already there would solve his
> > application problem, I'm just trusting his word on that.
> > Mike
>
> > On Thu, Sep 10, 2009 at 7:07 AM, Javier Montani <[email protected]>wrote:
>
> >> Did you try doing something like:
> >> update user_records_table
> >> set company= (select company from the_other_table where .....[may be
> >> user_records_table.name =the_other_table .name] ...)
>
> >> 2009/9/9 JJ <[email protected]>
>
> >>> 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 (the
> >>> name that is already in it) 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
>
> >>> Or would I have to use variables to loop through the list of records
> >>> in some manner?
>
> >>> Thanks in advance- Hide quoted text -
>
> - Show quoted text -
It may be something as screwy as having a table, populated by a
trigger, to provide display values to the app:
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>
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
