MySQL Shell API  8.0.17
Unified development interface for MySQL Products
Methods | List of all members
TableUpdate Class Reference

Handler for record update operations on a Table. More...

Methods

TableUpdate update ()
 Initializes the update operation. More...
 
TableUpdate set (String attribute, Value value)
 Adds an update operation. More...
 
TableUpdate where (String expression)
 Sets the search condition to filter the records to be updated. More...
 
TableUpdate orderBy (List sortExprStr)
 Sets the order in which the records will be updated. More...
 
TableUpdate limit (Integer numberOfRows)
 Sets the maximum number of rows to be updated by the operation. More...
 
TableUpdate bind (String name, Value value)
 Binds a value to a specific placeholder used on this operation. More...
 
Result execute ()
 Executes the delete operation with all the configured options. More...
 

Detailed Description

Handler for record update operations on a Table.

This object provides the necessary functions to allow updating records on a table.

This object should only be created by calling the update function on the table object on which the records will be updated.

See also
Table

Member Function Documentation

◆ update()

TableUpdate update ( )

Initializes the update operation.

Returns
This TableUpdate object.

Method Chaining

After this function invocation, the following function can be invoked:

See also
Usage examples at execute().

◆ set()

TableUpdate set ( String  attribute,
Value  value 
)

Adds an update operation.

Parameters
attributeIdentifies the column to be updated by this operation.
valueDefines the value to be set on the indicated column.
Returns
This TableUpdate object.

Adds an operation into the update handler to update a column value in on the records that were included on the selection filter and limit.

Using Expressions for Values

The expression also can be used for Parameter Binding.

Method Chaining

This function can be invoked multiple times after:

After this function invocation, the following functions can be invoked:

See also
Usage examples at execute().

◆ where()

TableUpdate where ( String  expression)

Sets the search condition to filter the records to be updated.

Parameters
expressionOptional condition to filter the records to be updated.
Returns
This TableUpdate object.

If used, only those rows satisfying the expression will be updated

The expression supports Parameter Binding.

Method Chaining

This function can be invoked only once after:

After this function invocation, the following functions can be invoked:

See also
Usage examples at execute().

◆ orderBy()

TableUpdate orderBy ( List  sortExprStr)

Sets the order in which the records will be updated.

Returns
This TableUpdate object.

If used the records will be updated in the order established by the sort criteria.

The elements of sortExprStr list are strings defining the column name on which the sorting will be based.

The format is as follows: columnIdentifier [ ASC | DESC ]

If no order criteria is specified, ASC will be used by default.

Method Chaining

This function can be invoked only once after:

After this function invocation, the following functions can be invoked:

◆ limit()

TableUpdate limit ( Integer  numberOfRows)

Sets the maximum number of rows to be updated by the operation.

Parameters
numberOfRowsThe maximum number of rows to be updated.
Returns
This TableUpdate object.

If used, the operation will update only numberOfRows rows.

Method Chaining

This function can be invoked only once after:

This function can be called every time the statement is executed.

After this function invocation, the following functions can be invoked:

See also
Usage examples at execute().

◆ bind()

TableUpdate bind ( String  name,
Value  value 
)

Binds a value to a specific placeholder used on this operation.

Parameters
nameThe name of the placeholder to which the value will be bound.
valueThe value to be bound on the placeholder.
Returns
This TableUpdate object.

Binds a value to a specific placeholder used on this operation.

An error will be raised if the placeholder indicated by name does not exist.

This function must be called once for each used placeholder or an error will be raised when the execute method is called.

Method Chaining

This function can be invoked multiple times right before calling execute:

After this function invocation, the following functions can be invoked:

An error will be raised if the placeholder indicated by name does not exist.

This function must be called once for each used placeholder or an error will be raised when the execute method is called.

See also
Usage examples at execute().

◆ execute()

Result execute ( )

Executes the delete operation with all the configured options.

Returns
A Result object.

Method Chaining

This function can be invoked after any other function on this class except update().

Examples

//@# TableUpdate: simple test
var result = table.update().set('name', 'aline').where('age = 13').execute();
print('Affected Rows:', result.affectedItemsCount, '\n');
var result = table.select().where('name = "aline"').execute();
record = result.fetchOne();
print("Updated Record:", record.name, record.age);
//@ TableUpdate: test using expression
var result = table.update().set('age', mysqlx.expr('13+10')).where('age = 13').execute();
print('Affected Rows:', result.affectedItemsCount, '\n');
var result = table.select().where('age = 23').execute();
record = result.fetchOne();
print("Updated Record:", record.name, record.age);
//@ TableUpdate: test using limits
var result = table.update().set('age', mysqlx.expr(':new_year')).where('age = :old_year').limit(2).bind('new_year', 16).bind('old_year', 15).execute();
print('Affected Rows:', result.affectedItemsCount, '\n');
var records = table.select().where('age = 16').execute().fetchAll();
print('With 16 Years:', records.length, '\n');
var records = table.select().where('age = 15').execute().fetchAll();
print('With 15 Years:', records.length, '\n');
//@ TableUpdate: test full update with view object
var view = schema.getTable('view1');
var result = view.update().set('my_gender', 'female').execute();
print('Updated Females:', result.affectedItemsCount, '\n');
// Result gets reflected on the target table
var records = table.select().where('gender = \"female\"').execute().fetchAll();
print('All Females:', records.length, '\n');