MYSQL Transform: Replacing date on historical input based on update of second input

jlazerus

I'm looking for the best way to do this. I have a SQL Transform with a recursive input and a second input which I'd like to have replace the input of the recursive data based on the date in one of the columns of the second input. The pictures below should help because I'm just as confused explaining this as you probably are reading it.

 

This image shows the historical data. Every day, the current date is appended to this dataset. Note the Date column (not batch date).

 

This one shows the monthly update. Note the Date and Batch Date columns. Since this file gets finalized after the end of the month, we want this to replace what's in the historical dataset using the Date column.

 

AS

Can you just join the new with the old on the GL account code and in the output use the new date in place of the old date?

 

Instead of 

SELECT 

old.`Date`, old.`GL Account`, old.`Gl Name`, old.`Balance`, old.`Batch Last Run`

FROM

historical as old

 

Use something like:

SELECT 

new.`Date`, old.`GL Account`, old.`Gl Name`, old.`Balance`, old.`Batch Last Run`

FROM

historical as old left join monthly as new on old.`Date` = new.`Date`

jlazerus

Would that replace the data in the `Balance` field for that date only? I still want to keep the rest of the data on the dataset.