Update large table based on substring

[aspdba]

Hi,

I have 1 table which has about 20 records...and I need to update another table which has 10 million records using column data from the small table.

eg:

table A(small table)

Column A Column B
312 412

table B(large table)

Column C
312456
312789

I need to update this table B, column C such that the updated values would be

412456
412789

So we would have to use functions (substring) in the update statement.

What would be the best approach for this?

Would function based index help? Is there a better way than using function based index?

Thanks
Sam

[slimdave]

Put a PK or UK on TableA.ColumnA, and you can ...

update 
(
select tableb.columnC,
         tableA.columnB
from tableA, tableB
where tableA.columnA = subStr(tableB.columnc,1,3)
)
set columnC = columnB||substr(columnC,4,3)
/

[tamilselvan]

Put a PK or UK on TableA.ColumnA, and you can ...

update 
(
select tableb.columnC,
         tableA.columnB
from tableA, tableB
where tableA.columnA = subStr(tableB.columnc,1,3)
)
set columnC = columnB||substr(columnC,4,3)
/

In line view update may not work because the result set of the join may have duplicate rows.

Tamil
www.beaconinfotechcorp.com

[ebrian]

The following update should work if you know that substr(columnC,1,3) is in tablea.columnA:

update tableb b
	set columnC = (select columnB||substr(b.col3,4,6)
			from tablea
			where tablea.columnA = substr(b.columnC,1,3));

If not, you can modify the update as follows:

update tableb b
	set columnC = (select columnB||substr(b.col3,4,6)
			from tablea
			where tablea.columnA = substr(b.columnC,1,3))
	where substr(columnC,1,3) in (select columnA from tablea);