Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Delete MySQL rows not found in original Excel Input

  1. #1
    Join Date
    Jun 2013
    Posts
    24

    Default Delete MySQL rows not found in original Excel Input

    Hi, I am new to kettle but making great progress

    I have a transformation written that successfully takes a spreadsheet of data and updates corresponding rows in a MySQL table.

    Now I find that part of the updated data is deleted rows that are no longer necessary. Is there a way to process this automatically? I know I could create an excel sheet of record IDs that need to be deleted and get rid of them that way, but I'd rather not have to manually figure out which ones those are.

    Thanks

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    If you don't want to delete rows by key, you can use a SQL job entry to execute a DELETE statement with a WHERE clause.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Jun 2013
    Posts
    24

    Default

    Thanks, that's what I was thinking but I can't get my head around how to specify what goes into the WHERE clause. What steps do I use to generate a list of IDs that aren't in the spreadsheet I start with? I imagine there will be another input somewhere that gets all records from the current database, then finds the ones that aren't in the spreadsheet input(?). But how...

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Well, in that case you are looking for the Diff step (category Join, named Merge Rows (diff)).

    BTW: The TI-source step in this graph would be your Excel Input.
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Nov 2008
    Posts
    2

    Default

    You can load into a temp table. Then left outer join target table and temp table to find out the missing ID (i.e. Target LEFT OUTER JOIN Temp ON Target.ID=Temp.ID AND Temp.ID IS NULL) and delete those rows all in same query.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.