{"id":3886,"date":"2023-09-21T14:14:20","date_gmt":"2023-09-21T13:14:20","guid":{"rendered":"https:\/\/help.expertsender.com\/?post_type=es&p=3886"},"modified":"2024-03-19T13:25:10","modified_gmt":"2024-03-19T12:25:10","slug":"updaterow","status":"publish","type":"es","link":"https:\/\/help.expertsender.com\/pl\/emp\/dynamic-content\/data-tables\/updaterow\/","title":{"rendered":"UpdateRow"},"content":{"rendered":"\n

UpdateRow<\/code> method updates a single row in a custom data table with new data. This is an equivalent of SQL UPDATE<\/code> query.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

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<\/code> and UpdateRow<\/code> methods.<\/p>\n\n\n\n

1. Method signature<\/strong><\/p>\n\n\n\n

<\/path><\/path><\/svg><\/span>
UpdateRow<\/span>(table<\/span>,<\/span> updates<\/span>,<\/span> filters<\/span>,<\/span> filterOperator)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Parameter definition:<\/p>\n\n\n\n
Name<\/th>Type<\/th>Description<\/th><\/tr><\/thead>
table<\/td>string<\/td>Required. Table name.<\/td><\/tr>
updates<\/td>array of Update objects<\/td>Required. Array of objects describing columns that will be updated with new values (equivalent of SET part of SQL UPDATE query).<\/td><\/tr>
filters<\/td>array of Filter objects<\/td>Optional. Array of objects defining elements of WHERE<\/code> phrase of UPDATE<\/code> 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<\/code> ). 
Default behavior can be overridden using  filterOperator<\/code>  parameter.<\/td><\/tr>
filterOperator<\/td>enumeration<\/td>Optional. 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…)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Update<\/code> object parameter definition<\/p>\n\n\n\n
Name<\/th>Type<\/th>Description<\/th><\/tr><\/thead>
name<\/td>string<\/td>Column name.<\/td><\/tr>
value<\/td>object<\/td>New 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.<\/td><\/tr>
type<\/td>enumeration<\/td>Update 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).<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Filter<\/code> object parameter definition<\/p>\n\n\n\n
Name<\/th>Type<\/th>Description<\/th><\/tr><\/thead>
Name<\/td>string<\/td>Column name<\/td><\/tr>
Modifier<\/td>enumeration<\/td>Operation modifier. Can be one of the following:EQ (equals, “=”)GT (greater than, “>”)LT (lower than, “<“)LIKE (SQL LIKE<\/code> operator)<\/td><\/tr>
Value<\/td>object<\/td>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.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Note: Filters work the same way as in GetRows<\/code> method. See GetRows documentation<\/a> for more details and examples.<\/p>\n\n\n\n
2. Usage and examples<\/strong><\/p>\n\n\n\n
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.<\/p>\n\n\n\n

Example 1 – the most simple call<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
$<\/span>{<\/span>UpdateRow<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new Update<\/span>(<\/span>'<\/span>MyColumn<\/span>'<\/span>,<\/span> 456<\/span>)}<\/span>)<\/span>}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
UPDATE <\/span>TOP<\/span>(<\/span>1<\/span>) MyTable SET MyColumn <\/span>=<\/span> <\/span>456<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
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.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Example 2 – updating single column in a row matching specified criteria<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
$<\/span>{<\/span>UpdateRow<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new Update<\/span>(<\/span>'<\/span>MyColumn<\/span>'<\/span>,<\/span> 456<\/span>)},<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new Filter<\/span>(<\/span>'<\/span>CustomerId<\/span>'<\/span>,<\/span> <\/span>EQ<\/span>,<\/span> 123<\/span>)}<\/span>)<\/span>}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
UPDATE <\/span>TOP<\/span>(<\/span>1<\/span>) MyTable SET MyColumn <\/span>=<\/span> <\/span>456<\/span> WHERE CustomerId <\/span>=<\/span> <\/span>123<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Example 3 – updating multiple columns<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
$<\/span>{<\/span>UpdateRow<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new Update<\/span>(<\/span>'<\/span>MyColumn<\/span>'<\/span>,<\/span> 456<\/span>),<\/span> new Update<\/span>(<\/span>'<\/span>ProductName<\/span>'<\/span>,<\/span> <\/span>'<\/span>Snowboard<\/span>'<\/span>)},<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new Filter<\/span>(<\/span>'<\/span>CustomerId<\/span>'<\/span>,<\/span> <\/span>EQ<\/span>,<\/span> 123<\/span>)}<\/span>)<\/span>}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
UPDATE <\/span>TOP<\/span>(<\/span>1<\/span>) MyTable SET MyColumn <\/span>=<\/span> <\/span>456<\/span>,<\/span> ProductName <\/span>=<\/span> <\/span>'<\/span>Snowboard<\/span>'<\/span> WHERE CustomerId <\/span>=<\/span> <\/span>123<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
It is of course possible to specify multiple filters – the equivalent of AND logical operator (or a different operator specified using filterOperator<\/code> parameter). It is also possible (and in most cases necessary) to match updated rows with subscriber properties, email etc.<\/p>\n\n\n\n
See GetRows method examples<\/a> for more information.<\/p>\n\n\n\n
Example 4 – incrementing column value<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
$<\/span>{<\/span>UpdateRow<\/span>(<\/span>'<\/span>MyTable<\/span>'<\/span>,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new Update<\/span>(<\/span>'<\/span>MyCounterColumn<\/span>'<\/span>,<\/span> 5<\/span>,<\/span> UpdateType.Increment<\/span>)},<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new Filter<\/span>(<\/span>'<\/span>CustomerId<\/span>'<\/span>,<\/span> <\/span>EQ<\/span>,<\/span> 123<\/span>)}<\/span>)<\/span>}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Equivalent SQL query:<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
UPDATE <\/span>TOP<\/span>(<\/span>1<\/span>) MyTable SET MyCounterColumn <\/span>=<\/span> MyCounterColumn <\/span>+<\/span> <\/span>5<\/span> WHERE CustomerId <\/span>=<\/span> <\/span>123<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
It is possible to perform multiple increments in a single call or mix regular updates and increments.<\/p>\n\n\n\n
Example 5 – real-life scenario, sending emails with single-use coupons<\/p>\n\n\n\n
<\/path><\/path><\/svg><\/span>
<!--<\/span> get coupon for customer and store <\/span>in<\/span> a variable <\/span>--><\/span><\/span>\n<<\/span>var<\/span> <\/span>rows<\/span>=<\/span>"<\/span>GetRows('Coupons', 1, null, new [] {new Filter('CustomerId', EQ, SubscriberProperty('customer_id')), new Filter('IsUsed', EQ, false)})<\/span>"<\/span>\/><\/span><\/span>\n<!--<\/span> display coupon code <\/span>in<\/span> email <\/span>--><\/span><\/span>\n<<\/span>p<\/span>><\/span>Hello! Here's your coupon: $<\/span>{<\/span>rows[<\/span>0<\/span>][<\/span>'<\/span>CouponCode<\/span>'<\/span>]<\/span>}<\/<\/span>p<\/span>><\/span><\/span>\n<!--<\/span> mark coupon <\/span>as<\/span> <\/span>used<\/span> <\/span>and<\/span> <\/span>save<\/span> <\/span>the<\/span> <\/span>date<\/span> <\/span>it<\/span> <\/span>was<\/span> <\/span>used<\/span> <\/span>on<\/span> <\/span>--><\/span><\/span>\n$<\/span>{<\/span>UpdateRow<\/span>(<\/span>'<\/span>Coupons<\/span>'<\/span>,<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new Update<\/span>(<\/span>'<\/span>IsUsed<\/span>'<\/span>,<\/span> <\/span>true<\/span>),<\/span> new Update<\/span>(<\/span>'<\/span>UsedOn<\/span>'<\/span>,<\/span> System.DateTime.UtcNow<\/span>)},<\/span> <\/span>new<\/span> [] <\/span>{<\/span>new Filter<\/span>(<\/span>'<\/span>CouponId<\/span>'<\/span>,<\/span> <\/span>EQ<\/span>,<\/span> <\/span>rows<\/span>[<\/span>0<\/span>][<\/span>'<\/span>CouponId<\/span>'<\/span>])}<\/span>)<\/span>}<\/span><\/span><\/code><\/pre><\/div>\n","protected":false},"author":6,"featured_media":0,"parent":3874,"menu_order":216,"template":"","format":"standard","emp-category":[],"class_list":["post-3886","es","type-es","status-publish","format-standard","hentry"],"acf":[],"_links":{"self":[{"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/es\/3886","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/es"}],"about":[{"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/types\/es"}],"author":[{"embeddable":true,"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/users\/6"}],"version-history":[{"count":2,"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/es\/3886\/revisions"}],"predecessor-version":[{"id":8869,"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/es\/3886\/revisions\/8869"}],"up":[{"embeddable":true,"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/es\/3874"}],"wp:attachment":[{"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/media?parent=3886"}],"wp:term":[{"taxonomy":"emp-category","embeddable":true,"href":"https:\/\/help.expertsender.com\/pl\/wp-json\/wp\/v2\/emp-category?post=3886"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}