| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: UPDATE Question...
A copy of this was sent to Angelica Veron <Faro_at_globalserve.net>
(if that email address didn't require changing)
On Sun, 27 Jun 1999 04:04:12 -0500, you wrote:
>Hello Everyone,
>
>UPDATE WrongTable
>SET WrongTable.employee_name = (
>SELECT CorrectTable.employee_name
>FROM CorrectTable
>WHERE CorrectTable.employee_id = WrongTable.employee_id);
>
>
>The above script updates Field 1 in Table 1 with the information of
>Field 2 in Table 2 (both being of the same datatype). At the moment
>Field 2 basically overwrites all existing data with it's own data, and
>if it encounters data in Field 1 which it doesn't have an equivalent
>update it just replaces it with a NULL value (ie. wipes out the original
>value of Field 1 even if it has none of it's own to update).
>
>My question is, can an addition be made to the above query such that it
>would leave any existing data in Field 1 if it does not have a value to
>update it with ?. This is because if Field 1 is NOT NULL type then the
>query would not run as an error would be reported of an attempt to
>insert a NULL value into a NOT NULL field.
>
>Also, on a separate note, does anyone have a script to run which would
>update Fields 1,2, and 3 from Table 1 into Fields 1,2,3 of Table 2 all
>in one go (i.e., not updating one column at a time, rather one row at a
>time), does anyone have a handy script for such a task ?. You can assume
>that Field 1 is the key to join, and also the fields are of the same
>data types. Thank you so much for your time.
>
this answers both questions using 2 different updates. pick the update that you like best (assuming the correct keys are in place for the second update that is -- the second update has the opportunity to be the faster of the two as it joins and updates in one step)
SQL> create table wrongTable
2 ( employee_name varchar2(25) , more_data int, employee_id int primary key
);
Table created.
SQL> create table correctTable
2 ( employee_name varchar2(25) , more_data int, employee_id int primary key
);
Table created.
SQL> insert into wrongTable values ( 'bad', -99, 1 ); SQL> insert into wrongTable values ( 'leave alone', 0, 2 ); SQL> SQL> insert into correctTable values ( 'good', 123, 1 ); SQL> commit;
SQL> select * from wrongTable;
EMPLOYEE_NAME MORE_DATA EMPLOYEE_ID
------------------------- ---------- ----------- bad -99 1 leave alone 0 2
SQL>
SQL> update wrongTable
2 set (employee_name, more_data) = 3 ( select correctTable.employee_name, correctTable.more_data 4 from correctTable 5 where correctTable.employee_id = 6 wrongTable.employee_id ) 7 where EXISTS ( select correctTable.employee_name, correctTable.more_data 8 from correctTable 9 where correctTable.employee_id = 10 wrongTable.employee_id )11 /
1 row updated.
SQL> select * from wrongTable;
EMPLOYEE_NAME MORE_DATA EMPLOYEE_ID
------------------------- ---------- ----------- good 123 1 leave alone 0 2
SQL> rollback;
Rollback complete.
SQL>
SQL> update
2 ( select w.employee_name w_name, c.employee_name c_name,
3 w.more_data w_more_data, c.more_data c_more_data 4 from wrongTable w, correctTable c 5 where w.employee_id = c.employee_id )6 set w_name = c_name, w_more_data = c_more_data 7 /
1 row updated.
SQL> select * from wrongTable;
EMPLOYEE_NAME MORE_DATA EMPLOYEE_ID
------------------------- ---------- ----------- good 123 1 leave alone 0 2
>Yours Sincerely,
>Angelica Veron,
>Faro_at_globalserve.net
>
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Jun 27 1999 - 08:59:02 CDT
 |
 |