I recently had a question answered on how to update table A's column B with values from table C's column D, respective to id's.

UPDATE Table2 t2
    SET D_N = COALESCE( (SELECT t1.D_N FROM Table1 t1 WHERE t1.D_ID 
= t2.D_ID), 0);

The code worked and now I'm trying to do the same, only the comparison isn't done on VARCHAR/VARCHAR2 fields but NUMBER. The fields i'm referring to are D_ID. The code worked on VARCHAR2/VARCHAR fields and now these are NUMBER.

I thought this would be easy to fix, so i created a temp column for each table and filled it with the values with the to_char method and my schema shows as expected, a column with data type VARCHAR2 for both Table1 and Table2 columns D_ID.

I still receive the error ORA-00932: inconsistent datatypes: expected CHAR got NUMBER though.

DDL for tables in question:

CREATE TABLE  "Table1" 
   (    
    "D_ID" NUMBER(4,0), 
    "D_N" VARCHAR2(30) COLLATE "USING_NLS_COMP" CONSTRAINT "D_N_NN" NOT NULL ENABLE, 
    "TEMPDEPTID" VARCHAR2(4) COLLATE "USING_NLS_COMP", 
     CONSTRAINT "D_ID_PK" PRIMARY KEY ("D_ID")
  USING INDEX  ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP"
/
ALTER TABLE  "Table1" ADD CONSTRAINT "D_L_FK" FOREIGN KEY ("L_ID")
      REFERENCES  "Table3" ("L_ID") ENABLE
/
ALTER TABLE  "Table1" ADD CONSTRAINT "D_M_FK" FOREIGN KEY ("M_ID")
      REFERENCES  "Table2" ("E_ID") ENABLE
/


CREATE TABLE  "Table2" 
   (    
    "D_ID" NUMBER(4,0), 
    "TEMPDEPTID" VARCHAR2(4) COLLATE "USING_NLS_COMP", 
    "D_N" VARCHAR2(30) COLLATE "USING_NLS_COMP", 
     CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("E_ID")
  USING INDEX  ENABLE, 

   )  DEFAULT COLLATION "USING_NLS_COMP"
/
ALTER TABLE  "Table2" ADD CONSTRAINT "EMP_D_FK" FOREIGN KEY ("D_ID")
      REFERENCES  "Table1" ("D_ID") ENABLE
/
ALTER TABLE  "Table2" ADD CONSTRAINT "EMP_M_FK" FOREIGN KEY ("M_ID")
      REFERENCES  "Table2" ("E_ID") ENABLE
/

Thanks.

1 Answers

0
van On

The 0 at the end of the COALESCE was causing this it seems,

UPDATE Table2 t2
    SET D_N = COALESCE( (SELECT t1.D_N FROM Table1 t1 WHERE t1.D_ID 
= t2.D_ID), 0);

changed to

UPDATE Table2 t2
    SET D_N = COALESCE( (SELECT t1.D_N FROM Table1 t1 WHERE t1.D_ID 
= t2.D_ID), '-');

did it for me.