Lots of update statements on table - best method? 1

[LGJ]

Hi all,

I have to update a table ACCOUNT with a msisdn and bdom (billing day of month) on production.

I have a flat file for example:

123456 26
231231 26
131231 26
312312 26

(This is the msisdn in column1 and bdom in column2.)

Normally I would then create a script which creates a file for example:

update ACCOUNT
set bdom=26
where msisdn='123456';

update ACCOUNT
set bdom=26
where msisdn='231231';

etc.. till all records done.

I would then load this in with SQL Plus.

Does this seem OK seeing as each file could have between 50,000 -> 200,000 update statements in?

Is there are more efficient way such as loading the data into a temp table and unloading this? If so could someone please talk me through this?

Thanks for any help. (newbie to SQL)

Lee

 

[LGJ]

ttt.

Any ideas guys or is this the best way?

Thanks

 

[sem]

Your second idea sounds much better. Use sql*loader to load data from your file to some staging table and then issue an update.

Regards, Dima

http://seminihin.narod.ru

 

[LGJ]

thanks sem.

Sorry but I dont actually know how to do this? It was just a thought, is this easy.

Can anyone show me the commands to do this please?

Thanks

 

[sem]

Create staging table:

create table account_stg(
msisdn number,
bdom number
)

Create control file my.ctl with the following content:

load data
infile '<data file name>'
APPEND
into table account_stg
( msisdn position(1:6)
, bdom position(9:10))

Then run sqlldr user/pass@db my.ctl

Then create unique index on account_stg.msisdn and run

update (select a.msisdn, b.bdom n, a.bdom o
from account a, account_stg b 
where a.msisdn=b.msisdn)
set o = n

or just

update account a set a.bdom =(select bdom from account_stg b
where a.msisdn=b.msisdn)

or anything else suiting your needs.

If eerything's OK drop table account_stg.

Regards, Dima

http://seminihin.narod.ru

 

[LGJ]

Sem/Dima,

Thanks for this post, great help.

LGJ

 

[LGJ]

I have tried this and get problems on the load, I am updating more than one field and my update looks like:

update (select a.account_no, b.bdom n, a.actg_cycle_dom o, b.actg_next_t p, a.actg_next_t q, b.actg_future_t r, a.actg_future_t s
from account_t a, account_stg b
where a.account_no=b.msisdn)
set o = n
set q = p
set s = r;

When running I get the following error:

set q = p
*
ERROR at line 5:
ORA-00933: SQL command not properly ended


Any ideas on where my syntax is wrong?
Thanks

LGJ

 

[sem]

..
set o = n
, q = p
, s = r;

Regards, Dima

http://seminihin.narod.ru

 

[LGJ]

hmmm,

doing that I get this though?

set o = n
*
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table

sorry for all the questions, I really appreciate the help.

LGJ

 

[sem]

Have you created UNIQUE index?

Regards, Dima

http://seminihin.narod.ru

 

[LGJ]

I have created a PRIMARY KEY on msisdn.

Missed the unique index bit, it says its already indexed now!

I get this now:

where a.account_no=b.msisdn)
*
ERROR at line 3:
ORA-01722: invalid number

 

[sem]

Is msisdn of NUMBER datatype? I suppose you've declared it as VARCHAR2 and now it contains invalid values. Or conversly, it is declared as NUMBER, but account_no contains non-numeric characters. They should (not must) be of the same type.
What's with your previous ORA-01779?

Regards, Dima

http://seminihin.narod.ru