I have a database with some missing information. I have a stream of data coming in from Pentaho. I'm trying to update the info based on the stream.
The table looks like
EMP_ID, Clock_IN, Clock_Out, Date,UUID
Some rows will have the Emp_ID, Clock_Out, and Date but be missing the Clock_In.
Some rows will have the Emp_ID, Clock_In and Date but be missing the Clock_In.
The incoming Rows will have EMP_ID and Clock_In and/or Clock_out.
I am generating the UUID as a step in the transformation.
When a row comes in I want it to look up the table and decide whether to update the Clock_In or Clock_Out, if the row exists, or create a new row if it doesn't.
How can I accomplish this?