Re: Merge - ora-00904 - Invalid Identifier.

robert.mcauley_at_eurocarparts.com schrieb:
> Hi Folks, I'm pretty new no Oracle and SQL and have been trying to
> learn as I go. Can someone please help?
> I have to "UPSERT" tables on a monthly basis from text files from a
> data supplier.
>
> I plan to replace each text file into staging table, Create a trigger
> on the staging table to "MERGE" the data onto a final table. I have
> had to do it this way as sqlldr will not alllow merge.
>
> I've been trying to get the merge working on it's own before inclusion
> in a trigger but am getting ORA-00904 - Invalid Identifier. I have
> checked and re-checked and cannot spot the error. Any clues anyone?
>
> MERGE INTO "LOAD_TEST_T"
> USING (SELECT * from "LOAD_TEST_IN")
> ON ("LOAD_TEST_T"."PZ"="LOAD_TEST_IN"."PZ")
> WHEN MATCHED THEN
> UPDATE
> SET "LOAD_TEST_T"."TEXT"="LOAD_TEST_IN"."TEXT"
> WHEN NOT MATCHED THEN
> INSERT
> ("LOAD_TEST_T"."PZ", "LOAD_TEST_T"."TEXT")
> VALUES
> ("LOAD_TEST_IN"."PZ", "LOAD_TEST_IN"."TEXT");
>
>
>
>
>
> I'll be extremely embarrassed if it's a typo.........
>

Try it like this

MERGE INTO "LOAD_TEST_T"
USING (SELECT * from "LOAD_TEST_IN") LOAD_TEST_IN ON ("LOAD_TEST_T"."PZ"="LOAD_TEST_IN"."PZ") WHEN MATCHED THEN
UPDATE
         SET "LOAD_TEST_T"."TEXT"="LOAD_TEST_IN"."TEXT" WHEN NOT MATCHED THEN
INSERT
         ("LOAD_TEST_T"."PZ", "LOAD_TEST_T"."TEXT") VALUES
         ("LOAD_TEST_IN"."PZ", "LOAD_TEST_IN"."TEXT"); or even more simply:

MERGE INTO "LOAD_TEST_T"
USING LOAD_TEST_IN
ON ("LOAD_TEST_T"."PZ"="LOAD_TEST_IN"."PZ") WHEN MATCHED THEN
UPDATE
         SET "LOAD_TEST_T"."TEXT"="LOAD_TEST_IN"."TEXT" WHEN NOT MATCHED THEN
INSERT
         ("LOAD_TEST_T"."PZ", "LOAD_TEST_T"."TEXT") VALUES
         ("LOAD_TEST_IN"."PZ", "LOAD_TEST_IN"."TEXT"); Best regards

Maxim Received on Wed Apr 25 2007 - 09:59:45 CDT