update using subquery

[lgaorcl]

I need to update a huge table with many years of data.
The data I need to update is for year 2004. The script I wrote is:

update /*+PARALLEL */ facttable f set (a, b) = (select a, b from load_facttable)
where c=f.c)

This script would update the whole table. How could I make it only update data only for year 2004?

Thanks.

[davey23uk]

Originally posted by lgaorcl
I need to update a huge table with many years of data.
The data I need to update is for year 2004. The script I wrote is:

update /*+PARALLEL */ facttable f set (a, b) = (select a, b from load_facttable)
where c=f.c)

This script would update the whole table. How could I make it only update data only for year 2004?

Thanks.

by adding a where clause on facttable which denotes 2004 data

[lgaorcl]

The problem is this script would update those not match c=f.c records to be null. How could I avoid this? I don't really have spefic year, just trying to use c=f.c to update a and b column.

Thanks.

[padders]

If loadfacttable is unique by 'c' you might consider...

UPDATE (
  SELECT f.a, f.b, 
         l.a new_a, l.b new_b
  FROM   loadfacttable l, facttable f
  WHERE  f.c = l.c)
SET a = new_a, b = new_b;

[lgaorcl]

Padders: Thank you very much! I test the script, it seems it worked.

One more question: How can select a from date_table where full_date='11/30/2004'? full_date column datatype is date.

I really appreciate your help!

[davey23uk]

Originally posted by lgaorcl
Padders: Thank you very much! I test the script, it seems it worked.

One more question: How can select a from date_table where full_date='11/30/2004'? full_date column datatype is date.

I really appreciate your help!

where full_date = to_date('11/30/2004','DD/MM/YYYY')

[lgaorcl]

Davey: I tried that one. It gave me error message: ora-1843: not a valid month.

[jmodic]

Can't you spot the typo when someone suggests something so trivial as in above, particualry when the error code says it so clearly?? Can't you use some plain common sense and try:

where full_date = to_date('11/30/2004','MM/DD/YYYY')

[lgaorcl]

jmodic: you are funny, but you are right. I got it right!

Thanks!

[waitecj]

where full_date = to_date('11/30/2004','DD/MM/YYYY')

It aint a typo if you live in the UK