0

I have two same tables from two different schema, db link between schema already created. The table in schema B (old) need to be merged to schema A(new). The table structure between 2 schemas are identical, except an additional timestamp column exist in schema A.The timestamp value is defaulted as SYSDATE when merge.

When i tried to run the following script in schema B, it complains with

ORA-00904: "A"."LAST_UPDATED_TIMESTAMP": invalid identifier

MERGE INTO LOOKUP_RANGE A
USING (SELECT RANGE_NAME,RANGE_TYPE,RANGE_MIN,RANGE_MAX,RANGE_DESC FROM LOOKUP_RANGE) B
ON (A.RANGE_NAME = B.RANGE_NAME AND A.RANGE_TYPE = B.RANGE_TYPE)
WHEN MATCHED THEN UPDATE SET A.RANGE_MIN = B.RANGE_MIN, A.RANGE_MAX = B.RANGE_MAX,A.LAST_UPDATED_TIMESTAMP = SYSDATE,A.RANGE_DESC = B.RANGE_DESC
WHEN NOT MATCHED THEN INSERT (A.RANGE_MIN,A.RANGE_MAX,A.LAST_UPDATED_TIMESTAMP,A.RANGE_DESC) VALUES (A.RANGE_MIN,A.RANGE_MAX,SYSDATE,A.RANGE_DESC);

The key column in ON is the primary key of table. Am I execute in wrong schema? It should run in schema A?

user2102665
  • 429
  • 2
  • 11
  • 26
  • you are not using any DBLink in the statement. Are those table names synonyms? –  Apr 10 '18 at 08:32
  • Yes, table name in two schemas is identical, only LAST_UPDATED_TIMESTAMP is the new column exist in schema A(new). – user2102665 Apr 10 '18 at 08:33
  • 1
    Your query only contains a single table without any schema prefix, so I don't understand what you mean with "tables are identical in two schemas". Both table names you use reference the same table in the current user's schema. There is no fully qualified table name in your query that would use a table from a different schema –  Apr 10 '18 at 08:39
  • Understood. How can i specified table name with schema prefix in the query? I do not wish to hardcode the schema name. – user2102665 Apr 10 '18 at 08:42
  • You want the same un-qualified name to reference two different tables in the same query? That's impossible –  Apr 10 '18 at 08:43
  • ok, i created a synonym in schema B to reference table in schema A, but still same error. CREATE SYNONYM LOOKUP_RANGE_synonym FOR ABC_DEV.LOOKUP_RANGE; – user2102665 Apr 10 '18 at 08:53
  • So now it's `merge into lookup_range_synonym a`? – William Robertson Apr 10 '18 at 14:04
  • no need to use synonym.I reference both table using database link, and it work – user2102665 Apr 11 '18 at 03:09

1 Answers1

0

The below code work perfectly. B_MIG_61_TO_74 is the database link created, OLD is the schema name of old schema, NEW is the schema name of new schema

MERGE INTO NEW.LOOKUP_RANGE A USING (SELECT RANGE_NAME,RANGE_TYPE,RANGE_MIN,RANGE_MAX,RANGE_DESC FROM OLD.LOOKUP_RANGE@DB_MIG_61_TO_74) B ON (A.RANGE_NAME = B.RANGE_NAME AND A.RANGE_TYPE = B.RANGE_TYPE) WHEN MATCHED THEN UPDATE SET A.RANGE_MIN = B.RANGE_MIN, A.RANGE_MAX = B.RANGE_MAX,A.LAST_UPDATED_TIMESTAMP = SYSDATE,A.RANGE_DESC = B.RANGE_DESC WHEN NOT MATCHED THEN INSERT (A.RANGE_NAME,A.RANGE_TYPE,A.RANGE_MIN,A.RANGE_MAX,A.LAST_UPDATED_TIMESTAMP,A.RANGE_DESC) VALUES (B.RANGE_NAME,B.RANGE_TYPE,B.RANGE_MIN,B.RANGE_MAX,SYSDATE,B.RANGE_DESC);

user2102665
  • 429
  • 2
  • 11
  • 26