Skip to content

DeleteRow

DeleteRow method can be used to delete a single row from custom data table. This is an equivalent of SQL DELETE query.

It is best to be very careful with this method to avoid losing your data by accident. Deleting multiple rows is disabled, to avoid deleting whole tables by mistake.

This method can be useful if it is necessary to delete records from your custom data when an email is sent. E.g. this may be a list of customers who did not received any offer or a list of unique offers that cannot be sent twice, etc.

1. Method signature

DeleteRow(table, filters, filterOperator)

Parameter definition:

NameTypeDescription
TablestringRequired. Table name.
Filtersarray of Filter objectsOptional. Array of objects defining elements of WHERE phrase of DELETE query. Use this parameter, to restrict deleting row only if it matches your criteria. If null, no criteria will be applied. If several filters are defined, by default, deleted 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…)

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 DeleteRow 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

${DeleteRow('MyTable')}

Equivalent SQL query:

DELETE TOP(1) FROM MyTable

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

Example 2 – deleting a row matching specified criteria

${DeleteRow('MyTable', new [] {new Filter('CustomerId', EQ, 123)})}

Equivalent SQL query:

DELETE TOP(1) FROM MyTable WHERE CustomerId = 123

Example 3 – using multiple filters

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

Equivalent SQL query:

DELETE TOP(1) FROM MyTable WHERE CustomerId = 123 AND MyColumn = 456

It is also possible (and in most cases necessary) to match deleted rows with subscriber properties, email etc.

See GetRows method examples for more information.

Example 4 – 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'))})"/>
<!-- display coupon code in email -->
<p>Hello! Here's your coupon: ${rows[0]['CouponCode']}</p>
<!-- delete the coupon so it cannot be used again -->
${DeleteRow('Coupons', new [] {new Filter('CouponId', EQ, rows[0]['CouponId'])})}
Privacy policy