Printable View
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 dataQuote:
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.
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.
If loadfacttable is unique by 'c' you might consider...
Code: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;
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')Quote:
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!
Davey: I tried that one. It gave me error message: ora-1843: not a valid month.
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')
jmodic: you are funny, but you are right. I got it right!
Thanks!
It aint a typo if you live in the UK :)Quote:
where full_date = to_date('11/30/2004','DD/MM/YYYY')
Let's see:Quote:
Originally posted by waitecj
It aint a typo if you live in the UK :)
means:Code:where full_date = to_date('11/30/2004','DD/MM/YYYY')
'11' => 'DD'
'30' => 'MM'
'2004' => 'YYY'
So what is a name of the 30th month in a year in UK? :confused: :p
the DD/MM/YYYY format rolls so easily off the fingers over here.
> So what is a name of the 30th month in a year in UK?
There isn't one as you well know. But if there was you could be certain it would be freezing cold and piss down with rain all the time.
hey come on, it snowed yesterday - not all bad :-)Quote:
Originally posted by padders
> So what is a name of the 30th month in a year in UK?
There isn't one as you well know. But if there was you could be certain it would be freezing cold and piss down with rain all the time.
I think post-christmas snow should be banned from all land with less than a 30 degree gradient.
But at least you'd be able to get fish and chips, pork pies, a trip to the pub, a decent cup of tea, quality TV comedy, a school lesson on evolution, ... um ... I'll think of some more stuff when I'm more awake.Quote:
Originally posted by padders
> So what is a name of the 30th month in a year in UK?
There isn't one as you well know. But if there was you could be certain it would be freezing cold and piss down with rain all the time.
...the ability to drive on the correct side of the road without being arrested? The list goes on...
And over here....Quote:
Originally posted by davey23uk
the DD/MM/YYYY format rolls so easily off the fingers over here.
I hate 'em both -- everyone here uses MM/DD/YYYY then some screwy organization like the INS throws a form at you needing DD/MM/YYYY. Tut. It's DD-Mon-YYYY everytime, if i have any choice.
Yup! 01/02/2005 is ambiguous.
If you have to pander to the illogical MDY order how about 'Mon DD, YYYY'?
(and don't listen to that **** about "every one says e.g. January 27"
" . . .
A real-live of nephew of my Uncle Sam,
Born on the the fourth of July.")
Agreed. Who came up with that crap anyway?
No more of that 01/27/2005 or even 27/01/2005... Why don't we all just use "The twenty seventh of january two thousand and five" and we'll all know where we stand :D
Of course if you really want to logical, you'd have to use the ISO format (approx: YYYY-MM-DD HH24:MI:SS) - which nobody uses!
Is their any limitations of this method.. as to the mapping columns in the update list should be one among a KEY?Quote:
Originally posted by padders
If loadfacttable is unique by 'c' you might consider...
Code: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;
Abhay.
Thinking about it logically, each row in "f" must have no more than a single row in "l", therefore the join between the two must include the PK of "l".Quote:
Originally posted by abhaysk
Is their any limitations of this method.. as to the mapping columns in the update list should be one among a KEY?
Abhay.
By the way, if this really is a very large fact table then it's going to be much more efficient to create a new table in a nologging mode then replace the existing one with it, 'cos the logging of the updates is going to be a system killer. Assuming that the old fact table is partitioned then this could also be done by replacing the old partitions one-by-one with new data using partition exchanges.
Well yes.. i would expect that to be so.. else it dosent make much sense..Quote:
Originally posted by slimdave
Thinking about it logically, each row in "f" must have no more than a single row in "l", therefore the join between the two must include the PK of "l".
My Q was not at all realted to that.. Just see the case below..
Code:
DBA> ed
Wrote file afiedt.buf
1 UPDATE
2 (
3 SELECT
4 *
5 FROM
6 (
7 SELECT /*+ Parallel(RPH1, 4) Parallel(RPH2, 4) */
8 A.PIN ,
9 NVL(RPH2.PIN, NVL(RPH1.PIN, 'UNKN')) UPD_PIN
10 FROM
11 SHIPMENT_LINE_BO A ,
12 REPORTING_PRODUCT_HIER RPH1 ,
13 REPORTING_PRODUCT_HIER RPH2
14 WHERE
15 A.PART_NO = RPH1.PIN(+) AND
16 RPH1.PH_LEVEL_NO(+) = '8' AND
17 A.BASE_PART_NO = RPH2.PIN(+) AND
18 RPH2.PH_LEVEL_NO(+) = '8' AND
19 A.PIN IS NULL
20 )
21 WHERE
22 UPD_PIN != 'UNKN'
23 )
24 SET
25* PIN = UPD_PIN
DBA> /
0 rows updated.
Elapsed: 00:00:00.60
DBA> ed
Wrote file afiedt.buf
1 UPDATE
2 (
3 SELECT
4 *
5 FROM
6 (
7 SELECT
8 So_Entry_Date,
9 NVL(D.RECORD_CREATE_DT, '??') Upd_So_Entry_Date
10 FROM
11 SHIPMENT_LINE_BO A ,
12 SLORDT D
13 WHERE
14 A.SALES_ORGANIZATION_CD = D.SALES_ORGANIZATION_CD(+) AND
15 A.REFERENCE_DOCUMENT_NO = D.SALES_ORDER_NO(+) AND
16 A.REFRENCE_DOCUMENT_ITEM_NO = D.SALES_ORDER_ITEM_NO(+) AND
17 SUBSTR(A.SOURCE_NM,-3,3) = SUBSTR(D.SOURCE_NM(+),-3,3)
18 )
19 WHERE
20 Upd_So_Entry_Date != '??'
21 )
22 SET
23* So_Entry_Date = Upd_So_Entry_Date
DBA> /
So_Entry_Date = Upd_So_Entry_Date
*
ERROR at line 23:
ORA-01779: cannot modify a column which maps to a non key-preserved table
Elapsed: 00:00:00.89
Here the "D.RECORD_CREATE_DT" is not a part of any key.. but "RPHx.PIN" is and that update statement ran fine.. So i asked if there is any constraint that the mapping column in update set list should be a part of KEY?
Abhay.
> Is their any limitations of this method.. as to the mapping columns in the update list should be one among a KEY?
Yes - as you have shown the join column in the *child* table must be unique with a PK, UK to prove it (although in fact a unique index is sufficient).
If you are feeling brave you can get around this restriction by using the undocumented /*+ BYPASS_UJVC */ hint but if child table has duplicates the rows in the parent table may be updated multiple times in non-deterministic fashion.
Note that the MERGE statement (post 9i) does not work the same way - instead it actually validates that the data is unique rather than checking for the presence of a unique constraint or index. Hence you could do a similar operation without needing a UK, PK or unique index.