Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Is there anyway to update if the field is null

  1. #1
    Join Date
    May 2013
    Posts
    19

    Default Is there anyway to update if the field is null

    Hey Guys,

    I want to update a table, but I don't want to overwrite fields already have a value in it. So is there anyway we can update fields which doesn't have value? I know we can probably filter them first then do it separately. But is there any quick and easy way to do that?

    Thanks

  2. #2
    Join Date
    Oct 2011
    Posts
    4

    Default

    You can always do a filter where the values are null as shown in the attached picture/kettle transformation TEST.ktr. Also if you are using a table input step you can use database specific syntax like IFNULL() for mysql, NVL for Oracle, or ISNULL() for SQL Server.

    Name:  Screen Shot 2013-11-06 at 1.05.45 AM.jpg
Views: 856
Size:  21.6 KB
    TEST.ktr

  3. #3
    Join Date
    Nov 2008
    Posts
    777

    Default

    You really don't need the Filter rows step in this example. The "If field value is null" will only act if the field is null. It will not overwrite existing data.

    The Update step and the Execute SQL script step are also options if the nulls are in a database table.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  4. #4
    Join Date
    May 2013
    Posts
    19

    Default

    hmm.... maybe my expression is a little confusing. Let me make an example:

    I'm reading from a text file and updating a table in database. Table has name and age. name is the key to match when updating. So I just want to update if the record in database doesn't have age value, otherwise just don't update age value.

    So basically I want to conditionally update. In general, if a value in database satisfy a condition (or a value in the text file satisfy a condition, such as not null or greater than a particular number), then I want to update that field.

    Is it possible?

  5. #5
    Join Date
    Nov 2009
    Posts
    688

    Default

    Why not use an input table step where you use : select * from table where age is null. Then join the text file with the input table step and you have only the records from the table where the age column is null. Then you can update the table with the age values from the text file.

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.