Skip to Content

UPSERT unique constraint violated between versions SP11 and SP12

Hi,

When we execute the below query in SAP HANA SP12 its throwing unique constraint violated error.

A,B,C are composite keys

CASE1:

upsert table1

(A,C,CREATED_BY ,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE ,BASE_STATUS,D)

select Q,J,CREATED_BY ,CREATED_DATE,'TEST',MODIFIED_DATE ,'1',E from table2;

We modified it to below in SP12 environment and its worked.

upsert table1

(A,B,C,CREATED_BY ,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE ,BASE_STATUS,D)

select Q,'',J,CREATED_BY ,CREATED_DATE,'TEST',MODIFIED_DATE ,'1',E from table2;

CASE2:

We ran the same code in SP11 and its not throwing any error.

upsert table1

(A,C,CREATED_BY ,CREATED_DATE,MODIFIED_BY,MODIFIED_DATE ,BASE_STATUS,D)

select Q,J,CREATED_BY ,CREATED_DATE,'TEST',MODIFIED_DATE ,'1',E from table2;

Is ther any changes in UPSERT query from SP11 to SP12?

Thanks,

Rajavelu

Add comment
10|10000 characters needed characters exceeded

  • table2 screenshot sample data attached..

    Lars Breddemann

    Can plz u check below.

    TABLE1 STRUCTURE:

    I have removed schema name and table and gave as table1

    generated sql..

    CREATECOLUMNTABLE"TABLE1" ("MANDT"NVARCHAR(3) DEFAULT'000'NOTNULL ,

    "SCENARIO_ID"NVARCHAR(20) DEFAULT''NOTNULL ,

    "DOC_NUMBER"NVARCHAR(20) DEFAULT''NOTNULL ,

    "PRICE_ZONE_ID"NVARCHAR(10) DEFAULT''NOTNULL ,

    "BASE_STATUS"NVARCHAR(1) DEFAULT''NOTNULL ,

    "WHAT_IF1"NVARCHAR(1) DEFAULT''NOTNULL ,

    "WHAT_IF2"NVARCHAR(1) DEFAULT''NOTNULL ,

    "WHAT_IF3"NVARCHAR(1) DEFAULT''NOTNULL ,

    "CREATED_BY"NVARCHAR(20) DEFAULT''NOTNULL ,

    "CREATED_DATE"NVARCHAR(8) DEFAULT'00000000'NOTNULL ,

    "MODIFIED_BY"NVARCHAR(20) DEFAULT''NOTNULL ,

    "MODIFIED_DATE"NVARCHAR(8) DEFAULT'00000000'NOTNULL ,

    PRIMARYKEY INVERTED VALUE ("MANDT",

    "SCENARIO_ID",

    "DOC_NUMBER",

    "PRICE_ZONE_ID")) UNLOADPRIORITY 5 AUTO MERGE



    createlocaltemporarytable #test1 ("MANDT"NVARCHAR(3) ,

    "DOC_NUMBER"NVARCHAR(20) ,

    "CREATED_BY"NVARCHAR(20) ,

    "CREATED_DATE"NVARCHAR(8) ,

    "MODIFIED_BY"NVARCHAR(20) ,

    "MODIFIED_DATE"NVARCHAR(8) ,

    "BASE_STATUS"NVARCHAR(1) ,

    "PRICE_ZONE_ID"NVARCHAR(10) );

    upsert"TABLE1"

    (MANDT ,DOC_NUMBER ,CREATED_BY

    ,CREATED_DATE ,MODIFIED_BY ,MODIFIED_DATE

    ,BASE_STATUS ,PRICE_ZONE_ID)

    select MANDT ,DOC_NUMBER ,CREATED_BY

    ,CREATED_DATE ,'ABC' ,MODIFIED_DATE

    ,'2' ,PRICE_ZONE_ID from #test1 ;

    capture.png (9.4 kB)
  • With available information the problem cannot be reproduced.

    Please provide the DDL for the tables and example data to reproduce the issue.

    If the non-key columns don't affect the outcome, please leave them out of your example.

  • Nice screenshot of your test data, but how do you think this will get inserted into a table in my DB?

  • Get RSS Feed

0 Answers