I am working on a delete statement as below:
DEL FROM X.SALES1
WHERE CAST(SALES_T_ID AS DECIMAL(18,0)) IN(
SEL CAST(T1.SALES_T_ID AS DECIMAL(18,0) )FROM
(
SEL * FROM
X.SALES1
QUALIFY ROW_NUMBER() OVER( PARTITION BY SALES_SRC_ID,DSTC ORDER BY UPDATED_DATE_TIMESTAMP DESC) >1
) T1
) ;
The above delete should keep the latest record and delete the rest. However, it is deleting all the records. I have checked the inner query and working fine, selecting only the records other than the latest updated one. But i am not able to understand why it's deleting all the records?
Below is the Table structure:
CREATE SET TABLE X.SALES1 ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
SALES_T_ID DECIMAL(18,0),
SALES_TRANS_NUM VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
SALES_SRC_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
SALES_TRANS_START_DTTM TIMESTAMP(0),
ST_EXPECTED_CLOSE_DTTM TIMESTAMP(0),
ST_ACTUAL_CLOSE_DTTM TIMESTAMP(0),
ST_ACT_CONT_DTTM TIMESTAMP(0),
ST_EXP_CONT_CLS_DTTM TIMESTAMP(0)
)
PRIMARY INDEX ( SALES_T_ID );
Sample data:
SALES_T_ID SALES_SRC_ID
715,603 3-3SDDJH5
593,803 3-3SDDJH5
715,604 3-3SDDJIG
593,804 3-3SDDJIG
715,605 3-3SDDJJR
My requirement is to keep only the latest row for the combination of Sales_trans_src_id and data_source_type_cd based on updated_date_timestamp.
DEL FROM X.SALES1
WHERE CAST(SALES_T_ID AS DECIMAL(18,0)) IN(
SEL CAST(T1.SALES_T_ID AS DECIMAL(18,0) )FROM
(
SEL * FROM
X.SALES1
QUALIFY rank() OVER( PARTITION BY SALES_SRC_ID,DSTC ORDER BY UPDATED_DATE_TIMESTAMP DESC) > 1
) T1
;
can you check this ) ;