I have a database that has the date as String and I have to delete the rows that have the same data in the csv file as in the database. More exactly, my dates look like this 2018-03-31T23:30:24+00:00. I want that when it gets a date like this, to delete from database where data LIKE %2018-03-31%, so it will delete all the records from that day, even if the time is not the same.
I have a job where tFileInputDelimited is connected with a tSortRow and then to tFlowToIterate. After that, I have a tJava where I extract the date and then a tMysqlInput where the query has the where clause like this: WHERE purchase_date LIKE '%"+context.date+"%' . Then, with a run if connection, I have tMysqlRow in which I have the delete statement with the same where clause. After that, of course, I have the tMysqlCommit.
The context.date is made like this:
context.dataaa=(String)globalMap.get("row6.purchase_date");
context.month=context.dataaa.substring(5,7);
context.year=context.dataaa.substring(0,4);
context.day=context.dataaa.substring(8,10);
context.date=context.year+"-"+context.month+"-"+context.day;
The problem is that, it doesn't delete from database. I want it to go row by row and delete all my records that have the same day in the csv and database.
The problem was from an if statement that compared the full date separately, so I had to compare only the day not day,year and month.