Pentaho

Your construction seems strange!

Every line that reachs a step are processed by it! There for if you dont whant to "update" a line... just dont send it to the update step!

You got a table input connected directly to a table output... and then you do a UPDATE on the same output table with a constant?

How do you define what is on your input? Did you cross tha table1 with table2 at the input to sort new lines? If so... why not put status on the query or in the constant before insert?

Based on what Im seeing... there is no need for update, just a better input and you could use a insert/update step to do pretty much the same, but there is always several ways to go.

Ok!

But how do I update only one row field that has changed?

Each updated row, the field status changes from "P" to "A".

In your second picture, you are using the field ID to determine which records you need to update. So this ID isn't a primary/unique key. To update only one line you need to add columns to the key lookup part of the UPDATE operator so you get only one line to update. If you add a second column like STATUS = P will you get only one line or will you still be getting more than one?

Does TABELA_02 have a primary key you can use to only update one line?

Regards

Hi Ana!

Yes, field1 is primary key and also in table_02.

How do I change only the changed field and also the status?

If possible, could you draw the structure? It can be really simple, just to understand.

Thank you!!

If ID is primary key, then you are going to update only one record for each ID.

Or if you pretend to update only one line, regardless of any other ID you are receiving in the stream of data, you can use the Unique rows step using a column that has the same value for all the stream of data so the output of the step is just one row. If you don't have such a column, you can add previously an Add constant step to create the column to do the trick.

Regards

Ana, when I enter a contraint, it updates all fields and not a row with the record changed only.

Something wrong in the structure?

Regards



The Unique Key step after the Add constants step is missing.

Regards

Ops... somethings wrong... when I post it there was no new flow... srry!

The point is your input is not compatible with your flow to reach the solution that you whants! If you have a "ID" as unique and you are updating based on it that means your flow are sending all rows to be updated by the same value (And that was the point that I was trying to explain in my 1st post)! So the problem is that you have a wrong flow for your dataset or the wrong dataset for yor flow!

To solve that you could get your "input table" and change it to make a query crossing your current input (query) with the destination table (with left join), that way all records from the input with a null field comming from the 2nd table are new (insert) and all that have value are alredy in destination so (update). You can use filter... case... or any way to set the field "A","P". Once you done that just use a single step "Insert / update" and thats it!

Sorry Ana, but where is this step?

Sorry, I wrote the name from memory, is the Unique Rows step. You can use the search in the Design tab to look for a certain step you want to use.

Regards