Re: How To Update A Field ?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 27 Jun 1999 09:53:18 +0200
Message-ID: <930469950.19832.0.pluto.d4ee154e@news.demon.nl>

Hi Angelica
Why don't you just exclude the null values like this UPDATE WrongTable
SET WrongTable.employee_name =
 (SELECT CorrectTable.employee_name
   FROM CorrectTable
   WHERE CorrectTable.employee_id = WrongTable.employee_id )
where exists
(select 'x'
 from CorrectTable c1
 where c1.employee_id = Wrongtable.employee_id

     and c1.employee_name is not null); Your other question should be resolved either this way update table1
set (field2, field3, field4 ) =
(select field2, field3, field4
 from table2
 where table2.field1 = table1.field1)
Or if that doesn't work, it should be resolved by a plsql block declare
cursor tab1curs is
select field1, field2, field3, field4
from table1
for update of field2, field3, field4;
tab2rec table2%ROWTYPE;
begin
for tab1rec in tab1curs loop

      select *
      into tab2rec
      from table2
     where table2.field1 = tab1rec.field1;
     update table1
     set field2 = tab2rec.field2
         , field3 = tab2rec.field3
     where current of tab1curs;

end loop;
commit;
end;
/

Hth,

Sybrand Bakker, Oracle DBA

Angelica Veron wrote in message <3775DFBA.A4D782A1_at_globalserve.net>...
>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.
>
>Yours Sincerely,
>Angelica Veron,
>Faro_at_globalserve.net
>
>
Received on Sun Jun 27 1999 - 02:53:18 CDT