Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: update with an external file
> I want to update a field in a table with an external text-file.
>
> If I want to update the filed I normally use this command:
> update zeichnung set migrstate='todo' where numer='1478741';
Presuming you want to update a row in a table ...
> This works fine, but not for 30000 rows. It is very slow.
Presuming you mean 30000 rows in the zeichnung table ..., guessing that there is no index on the "numer" column, or that if there is an index, it is not used (perhaps due to a character to number conversion)
But you probably mean that you issue 30000 individual update statements (your wrist must hurt after a long day at work ;)
> So I want to update the field migrstate with an external text-file
> The external text-file has only one row (the filed number) for example:
> 1478741
> 8754874
> 5897445
Presuming you mean one column (instead of one row)
> All those numbers exist in the database. So I want to update the field
> migrstate='todo' for all those numbes in the text-file, but I don't know how :-((
This should be very easy to do - using an external table definition.
You define a table, with one column, as external (pointing to a file on
the server where your database resides, using SQL Loader syntax). On
the OS side, this file can then be updated/refreshed without having to
modify the table definition. Inside the database, you can reference
this table just like if it was a normal table (for select only, and no
indexes). So you would end up with an update statement like ...
update zeichnung set migrstate='todo'
where numer in (Select <col1> from <external_table>);
I think that you should define <col1> to be the same datatype as the migrstate column.
HTH. Received on Wed Sep 13 2006 - 05:27:47 CDT
![]() |
![]() |