Update Table from another Table deppending of two Variables

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Update Table from another Table deppending of two Variables

Hi community...

I need to resolve this very quickly.

I established an extended table for CONTACT_HISTORY table of Customer Intelligence Common Data Model. I want to update a variable, this is LAST_CONTACT_DT.

HERE IS THE EXTENDED TABLE:

CELL_PACKAGE_SK     NUMBER(10) NOT NULL ,

CAMPAIGN_CD NVARCHAR2(30) NOT NULL,

CUSTOMER_ID         NUMBER(10) NOT NULL ,

LAST_CONTACT_DT DATE NOT NULL,

CICLO_ID NVARCHAR2(2) NOT NULL,

END_MANAGEMENT_DT DATE NOT NULL

For each occurrence for every communication for some campaign, if the Customer exist, i need to update the value of the variable.

First, I try this...

PROC SQL;

  UPDATE LIB.CI_CONTACT_HISTORY_C_EXTENDED SET LAST_CONTACT_DT = &FECHA. WHERE CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM EXIST);

QUIT;

That code, update the value for each Customer in the table, but for each campaign (CAMPAIGN_CD) that the customer exist. I only need to update the variable which contain the same END_MANAGEMENT_DT f the campaign that is being processed .. so.. I posed the following:

PROC SQL;

UPDATE LIB.CI_CONTACT_HISTORY_C_EXTENDED SET LAST_CONTACT_DT = &FECHA.

FROM LIB.CI_CONTACT_HISTORY_C_EXTENDED

     INNER JOIN WORK.EXIST EXISTS

  ON EXTENDED.CUSTOMER_ID = EXISTS.CUSTOMER_ID AND

  DATEPART(EXTENDED.END_MANAGEMENT_DT) = EXISTS.CONTACT_DT;

QUIT; /*The datepart function is required. WORK is SAS Code and the extended table is ORACLE Schema.*/

But this, doesn´t work.

Has anybody knows how to do that? How to realice an update from inner join between this tables: which contains the Customers in the occurrence of the communication and the table which contains that customers for the last occurrence of the previous communication.


Accepted Solutions
Solution
‎11-10-2014 02:09 PM
Super Contributor
Posts: 578

Re: Update Table from another Table deppending of two Variables

PROC SQL;

  UPDATE LIB.CI_CONTACT_HISTORY_C_EXTENDED T1 SET LAST_CONTACT_DT = &FECHA.

  WHERE EXISTS (SELECT * FROM EXIST WHERE CUSTOMER_ID=T1.CUSTOMER_ID AND CONTACT_DT  = DATEPART(T1.END_MANAGEMENT_DT));

QUIT;

View solution in original post


All Replies
Solution
‎11-10-2014 02:09 PM
Super Contributor
Posts: 578

Re: Update Table from another Table deppending of two Variables

PROC SQL;

  UPDATE LIB.CI_CONTACT_HISTORY_C_EXTENDED T1 SET LAST_CONTACT_DT = &FECHA.

  WHERE EXISTS (SELECT * FROM EXIST WHERE CUSTOMER_ID=T1.CUSTOMER_ID AND CONTACT_DT  = DATEPART(T1.END_MANAGEMENT_DT));

QUIT;

New Contributor
Posts: 2

Re: Update Table from another Table deppending of two Variables

Excelente... Muchas Gracias por el aporte...

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 188 views
  • 0 likes
  • 2 in conversation