Need help on UPDATE to INSERT - SELECT

Database

Need help on UPDATE to INSERT - SELECT

IM ON CONVERTING UPDATE STATEMENTS TO INSERTSELECT.

IN BELOW CASE, I WAS TRYING TO MERGE TWO UPDATE STATEMENTS INTO A SINGLE INSERT-SELECT STATEMENT,

BUT SINCE THE OTHER FIELD IS DEPENDENT ON THE FIRST FIELD, IM CAME UP WITH TWO INSERT - SELECTS.

IS THERE ANY SIMPLE SOLUTION TO HAVE SINGLE INSERTSELECT INSTEAD OF TWO.

---CODE WITH TWO UPDATE STATEMENTS

INSERT INTO TABLE_CUST

(

MO_ID,

XA_CUST_ID

)

SELECT

mth_dt,

XA_CUST_ID

FROM temp_table;

UPDATE TABLE_CUST

FROM

(SELECT xa_cust_id

FROM temp_table

WHERE evnt_dt BETWEEN start_dt AND end_dt

GROUP BY 1)

AS d(d1)

SET ACT_1_MO_IND = 1

WHERE xa_cust_id = d.d1

AND MO_ID = mth_dt ;

UPDATE TABLE_CUST

FROM

(SELECT p2p.MO_ID,

olb.xa_cust_id

FROM eligible olb

JOIN TABLE_CUST p2p

ON olb.xa_cust_id = p2p.xa_cust_id

AND p2p.ACT_1_MO_IND = 1

AND p2p.MO_ID = mth_dt

WHERE olb.enroll_dt IN (mth_dt)

AND olb.olb_act_dy_cd = 30

GROUP BY 1, 2)

AS d(d1,d2)

SET ACTVN_WTHN_1_MO_IND = 1

WHERE xa_cust_id = d.d2

AND MO_ID = d.d1 ;

---------------------------------------------------------------------

-----CONVERTED CODE

---------------------------------------------------------------------

INSERT INTO TABLE_CUST_S

(

MO_ID,

XA_CUST_ID

)

SELECT

mth_dt,

XA_CUST_ID

FROM temp_table;

 

CREATE VOLATILE TABLE 1mo_act_flg AS

(

SELECT xa_cust_id

FROM temp_table

WHERE bev_evnt_dt BETWEEN $start_dt AND $end_dt

GROUP BY 1

)WITH DATA ON COMMIT PRESERVE ROWS;

 

rename table TABLE_CUST_S TO TABLE_CUST_TMP;

create table TABLE_CUST_S AS TABLE_CUST_TMP with no data;

INSERT INTO TABLE_CUST_S

(

MO_ID,

XA_CUST_ID,

ACT_1_MO_IND

)

SEL

MO_ID,

TMP.XA_CUST_ID,

CASE WHEN TMP.XA_CUST_ID = 1MO.XA_CUST_ID THEN 1 ELSE TMP.ACT_1_MO_IND END AS ACT_1_MO_IND

FROM

TABLE_CUST_TMP TMP

LEFT OUTER JOIN 1mo_act_flg1MO

ON

TMP.XA_CUST_ID = 1MO.XA_CUST_ID;

--drop table

DROP TABLE TABLE_CUST_TMP;

CREATE VOLATILE TABLE actvn_1mo_flg AS

(

SELECT p2p.mo_id,

olb.xa_cust_id AS xa_cust_id

FROM eligible olb

JOIN TABLE_CUST_S p2p

ON olb.xa_cust_id = p2p.xa_cust_id

AND p2p.ACT_1_MO_IND = 1

AND p2p.mo_id = mth_dt

WHERE olb.enroll_dt IN (mth_dt)

AND olb.olb_act_dy_cd = 30

GROUP BY 1, 2

)WITH DATA ON COMMIT PRESERVE ROWS;

rename table TABLE_CUST_S TO TABLE_CUST_TMP;

create table TABLE_CUST_S AS TABLE_CUST_TMP with no data;

INSERT INTO TABLE_CUST_S

(

MO_ID,

XA_CUST_ID,

ACT_1_MO_IND,

ACTVN_WTHN_1_MO_IND

)

SEL

TMP.MO_ID,

TMP.XA_CUST_ID,

ACT_1_MO_IND,

CASE WHEN TMP.XA_CUST_ID = ACT1MO.XA_CUST_ID THEN 1 ELSE TMP.ACTVN_WTHN_1_MO_IND END AS ACTVN_WTHN_1_MO_IND

FROM

TABLE_CUST_TMP TMP

LEFT OUTER JOIN actvn_1mo_flg ACT1MO

ON

TMP.XA_CUST_ID = ACT1MO.XA_CUST_ID;

--drop table

DROP TABLE TABLE_CUST_TMP;

2 REPLIES

Re: Need help on UPDATE to INSERT - SELECT

Can any1 help me with this, thanks in advance..

N/A

Re: Need help on UPDATE to INSERT - SELECT

Nobody wants to read a bunch of unformatted code without any additional details.

You can probably rewrite it to a single INS/SEL, it seems to be just another LEFT JOIN.

Dieter