Перейти к содержимому

UpdateRow

UpdateRow method updates a single row in a custom data table with new data. This is an equivalent of SQL UPDATE query.

It is possible to update a single column or multiple columns with one query. Updating multiple rows is disabled, to avoid damaging large amounts of data by mistake.

This method may be useful if you need to mark in your custom data that an email was sent to particular subscriber. You may want to save sending date, or generate emails with single-use offers (such as unique coupons), using both GetRows and UpdateRow methods.

1. Method signature

UpdateRow(table, updates, filters, filterOperator)

Parameter definition:

NameTypeDescription
tablestringRequired. Table name.
updatesarray of Update objectsRequired. Array of objects describing columns that will be updated with new values (equivalent of SET part of SQL UPDATE query).
filtersarray of Filter objectsOptional. Array of objects defining elements of WHERE phrase of UPDATE query. Use this parameter, to restrict updating row only if it matches your criteria. If null, no criteria will be applied. If several filters are defined, by default, updated row will match all of them (equivalent to logical AND ). 
Default behavior can be overridden using  filterOperator  parameter.
filterOperatorenumerationOptional. Logical operator used for filters. Can be one of the following:AND (filter1 AND filter2 AND filter3…) — this is the default valueOR (filter1 OR filter2 OR filter3…)ANDNOT (NOT filter1 AND NOT filter2 AND NOT filter3…)

Update object parameter definition

NameTypeDescription
namestringColumn name.
valueobjectNew column value. Value type depends on column type, e.g. when filtering by number column, integer value should be supplied, string for text columns etc. Simple type conversion is also supported, e.g. string «123» representing correct integer value 123 will be accepted. This also works for dates, floating point numbers etc.
typeenumerationUpdate type. Optional. Valid values are:UpdateType.Update (Value substitution, e.g. «x = value». This is the default value.)UpdateType.Increment (Value incrementation, e.g. «x = x + value».)NOTE: Increments are not suitable for all column types. Supported types are Number and Double. String and Text types will work as well (string concatenation will be performed).

Filter object parameter definition

NameTypeDescription
NamestringColumn name
ModifierenumerationOperation modifier. Can be one of the following:EQ (equals, «=»)GT (greater than, «>»)LT (lower than, «<«)LIKE (SQL LIKE operator)
ValueobjectColumn value. Value type depends on column type, e.g. when filtering by number column, integer value should be supplied, string for text columns etc. Simple type conversion is also supported, e.g. string «123» representing correct integer value 123 will be accepted. This also works for dates, floating point numbers etc.

Note: Filters work the same way as in GetRows method. See GetRows documentation for more details and examples.

2. Usage and examples

You can call UpdateRow method using curly braces syntax. You may put in anywhere in the email template code since its output is empty string.


Example 1 — the most simple call

${UpdateRow('MyTable', new [] {new Update('MyColumn', 456)})}

Equivalent SQL query:

UPDATE TOP(1) MyTable SET MyColumn = 456

This however has limited use, since in most cases, you will want to update a specific row (and not first found). Following examples show how to use filters to achieve this.

Example 2 — updating single column in a row matching specified criteria

${UpdateRow('MyTable', new [] {new Update('MyColumn', 456)}, new [] {new Filter('CustomerId', EQ, 123)})}

Equivalent SQL query:

UPDATE TOP(1) MyTable SET MyColumn = 456 WHERE CustomerId = 123

Example 3 — updating multiple columns

${UpdateRow('MyTable', new [] {new Update('MyColumn', 456), new Update('ProductName', 'Snowboard')}, new [] {new Filter('CustomerId', EQ, 123)})}

Equivalent SQL query:

UPDATE TOP(1) MyTable SET MyColumn = 456, ProductName = 'Snowboard' WHERE CustomerId = 123

It is of course possible to specify multiple filters — the equivalent of AND logical operator (or a different operator specified using filterOperator parameter). It is also possible (and in most cases necessary) to match updated rows with subscriber properties, email etc.

See GetRows method examples for more information.

Example 4 — incrementing column value

${UpdateRow('MyTable', new [] {new Update('MyCounterColumn', 5, UpdateType.Increment)}, new [] {new Filter('CustomerId', EQ, 123)})}

Equivalent SQL query:

UPDATE TOP(1) MyTable SET MyCounterColumn = MyCounterColumn + 5 WHERE CustomerId = 123

It is possible to perform multiple increments in a single call or mix regular updates and increments.

Example 5 — real-life scenario, sending emails with single-use coupons

<!-- get coupon for customer and store in a variable -->
<var rows="GetRows('Coupons', 1, null, new [] {new Filter('CustomerId', EQ, SubscriberProperty('customer_id')), new Filter('IsUsed', EQ, false)})"/>
<!-- display coupon code in email -->
<p>Hello! Here's your coupon: ${rows[0]['CouponCode']}</p>
<!-- mark coupon as used and save the date it was used on -->
${UpdateRow('Coupons', new [] {new Update('IsUsed', true), new Update('UsedOn', System.DateTime.UtcNow)}, new [] {new Filter('CouponId', EQ, rows[0]['CouponId'])})}
Privacy policy