Updating data in a dataset based on date

tjlew903

Hi Everyone. 

 

Long time lurker first time poster (please be gentle)

 

I have a dataset that contains a lot of customer data (not to brag or anything) and I'm trying to update the customerID column for one specific customer from 123 to 456 only when another column "event date and time" is before 8/1/18. Essentially, I want all data for customer 123 to seem like it was for 456 for events that happened before 8/1/18.  

In SQL I’d simply say: update TABLE set CustomerID = '456' where "CustomerId" = '123' and "Event Date and Time" < '8/1/2018 %’ but I’m not sure the best way to do it in Domo. ETL? Redshift? 

 

I'm probably missing something obvious. Any help is greatly appreciated.

Property_Ninja

Hi tjlew,

 

I believe the best way to do this would be in either a MySQL or Redshift flow. You can simply do something like

 

select 
column1,
column2,
column3,
`event date and time`,
CASE WHEN `event date and time` < '2018-08-01' and customerID = 123 then 456 else customerID end as customerID
from customertable

 

Possibly a slightly more techincal approach would be to write a stored procedure in a MySQL dataflow.

CREATE PROCEDURE UPDATE_TABLE()

UPDATE `customertable` SET `customerID` = '456' WHERE `customerID` = 123 and `event date and time` < '2018-08-01';

END

-- Then create a new step that has "CALL UPDATE_TABLE" to actually run the command

 

Hope this helps,

 

Brian

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.