TableUpdate Class Reference

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

Methods
TableUpdate	update ()
	Initializes the update operation. More...
TableUpdate	set (str attribute, Value value)
	Adds an update operation. More...
TableUpdate	where (str expression)
	Sets the search condition to filter the records to be updated. More...
TableUpdate	order_by (list sortCriteria)
	Sets the order in which the records will be updated. More...
TableUpdate	limit (int numberOfRows)
	Sets the maximum number of rows to be updated by the operation. More...
TableUpdate	bind (str 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:

• set(str attribute, Value value)

  See also

  Usage examples at execute().

set()

TableUpdate set	(	str	attribute,
		Value	value
	)

Adds an update operation.

Parameters

attribute	Identifies the column to be updated by this operation.
value	Defines 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 the records that were included on the selection filter and limit.

Using Expressions As Values

If a mysqlx.expr(...) object is defined as a value, it will be evaluated in the server, the resulting value will be set at the indicated column.

The expression also can be used for Parameter Binding.

Method Chaining

This function can be invoked multiple times after:

• update()
• set(str attribute, Value value) After this function invocation, the following functions can be invoked:
• set(str attribute, Value value)
• where(str expression)
• order_by(list sortCriteria), order_by(str sortCriterion[, str sortCriterion, ...])
• limit(int numberOfRows)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

where()

TableUpdate where

(

str

expression

)

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

Parameters

expression

A 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:

• set(str attribute, Value value) After this function invocation, the following functions can be invoked:
• order_by(list sortCriteria), order_by(str sortCriterion[, str sortCriterion, ...])
• limit(int numberOfRows)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

order_by()

TableUpdate order_by

(

list

sortCriteria

)

Sets the order in which the records will be updated.

Returns

This TableUpdate object.

If used, the TableUpdate operation will update the records in the order established by the sort criteria.

Every defined sort criterion follows the format:

name [ ASC | DESC ]

ASC is used by default if the sort order is not specified.

Method Chaining

This function can be invoked only once after:

• set(str attribute, Value value)
• where(str expression) After this function invocation, the following functions can be invoked:
• limit(int numberOfRows)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

limit()

TableUpdate limit

(

int

numberOfRows

)

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

Parameters

numberOfRows

The 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:

• set(str attribute, Value value)
• where(str expression)
• order_by(list sortCriteria), order_by(str sortCriterion[, str sortCriterion, ...]) This function can be called every time the statement is executed.

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

• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

bind()

TableUpdate bind	(	str	name,
		Value	value
	)

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

Parameters

name	The name of the placeholder to which the value will be bound.
value	The value to be bound on the placeholder.

Returns

This TableUpdate object.

Binds the given value to the placeholder with the specified name.

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:

• bind(str name, Value value)
• execute()

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

Updating a single field in a record

result = result = table.update().set('name', 'aline').where('age = 13').execute()
print('Affected Rows:', result.affected_items_count, '\n')
 
result = table.select().where('name = "aline"').execute()
record = result.fetch_one()
print("Updated Record:", record.name, record.age)

Updating a single field using expressions

result = table.update().set('age', mysqlx.expr('13+10')).where('age = 13').execute()
print('Affected Rows:', result.affected_items_count, '\n')
 
result = table.select().where('age = 23').execute()
record = result.fetch_one()
print("Updated Record:", record.name, record.age)

Updating a single field using expressions and parameter binding

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.affected_items_count, '\n')
 
records = table.select().where('age = 16').execute().fetch_all()
print('With 16 Years:', len(records), '\n')
 
records = table.select().where('age = 15').execute().fetch_all()
print('With 15 Years:', len(records), '\n')

Updating a view

result = view.update().set('my_gender', 'female').execute()
print('Updated Females:', result.affected_items_count, '\n')
 
# Result gets reflected on the target table
records = table.select().where('gender = "female"').execute().fetch_all()
print('All Females:', len(records), '\n')