# DatabaseLookupAndUpdate

Looks up and updates a value in a database.

The DatabaseLookupAndUpdate function returns a single value of the specified column that satisfies the specified condition and updates the value according to the expression in the Update parameter. This is useful for example when reading rows from a database to process and at the same time indicating they have been processes so they won't be processed again. The update occurs inside of the current database transaction (along with all other database activity during the execution of the map).

Only certain functions are translated into SQL for evaluation by the database and can be used with DatabaseColumn and DatabaseFunction functions. These are:

* Add
* And
* Concat
* Constant
* Divide
* Multiply
* Or
* Subtract
* Not

That is to say that the DatabaseColumn and DatabaseFunctions must only be children of one of the above functions.

You can also use any function as part of the expression tree to reference elements from your map. You just can't have the DatabaseColumn or DatabaseFunction be a child of functions other than on the list above.

## Arguments

| Arguments | Definitions |
| --- | --- |
| Condition (Boolean) | The "where" condition for this lookup. This is an expression that<br>includes DatabaseColumn (and<br>maybe DatabaseFunction)<br>functions to specify to the database the selection criteria for the<br>lookup. Use the normal functions for specifying conditions (e.g.<br>Equal, And, Add). These functions will be translated into the<br>corresponding SQL for evaluation by the database. You may use any<br>other type of function in the Condition as well to refer to elements<br>in the map for example. |
| Update (Simple) | An expression that specifies the value of the output column after the<br>lookup. This is an expression that includes<br>DatabaseColumn (and maybe<br>DatabaseFunction) functions to specify<br>the value of the output column. Use the normal functions for<br>specifying conditions (fo example Equal,<br>And, Add). These<br>functions will be translated into the corresponding SQL for<br>evaluation by the database. You may use any other type of function<br>in the Update as well to refer to elements in the map for<br>example. |
| Output Column (String) | The output column to select. This is mainly used when you want to<br>use a function with the output column. For example, selecting the<br>maximum value of a column. Use a DatabaseFunction to specify the function and a<br>single DatabaseColumn within<br>that to specify the column. |

## Properties

| Properties | Definitions |
| --- | --- |
| Output Column | The fully qualified column whose value is to be returned by this<br>function. The specification is of the form: &lt;path to<br>database&gt;.&lt;table&gt;.&lt;column&gt; for<br>example: /Default<br>Project/Databases/MyDatabase.MYTABLE.MYCOLUMN. Note<br>that when using Talend Studio a graphical interface specifies this, so you don't need to be<br>concerned with this format. |
| Cache Lookups? | Specifies if the values returned by this function are cached.<br>Possible values are:<br><br><ul> <li> <kbd>DEFAULT</kbd> - cache                                        according to the caching policy of the runtime.</li> <li> <kbd>ALWAYS</kbd> - always cache                                        regardless of the runtime setting.</li> <li> <kbd>NEVER</kbd> - never cache                                        values regardless of the runtime setting.</li> </ul><br><br><br>The runtime also has a cache timeout value which invalidates<br>cached items after the expiration of the timeout value. |