Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: update with an external file

Re: update with an external file

From: G Quesnel <dbaguy_ott_at_yahoo.com>
Date: 13 Sep 2006 03:27:47 -0700
Message-ID: <1158143267.155050.208860@d34g2000cwd.googlegroups.com>


> 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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US