How can I delete items from multiple groups in a single SQL query?

You can use standard SQL OR logic within your WHERE clause, for example: 'DELETE FROM FQN.Customer WHERE $Group$ = 'GroupA' OR $Group$ = 'GroupB'. Alternatively, for better code maintainability, you can execute individual queries sequentially.

Is indexing required for the $Group$ keyword to work in SQL?

Yes. The class being queried must be indexed for SQL queries to work. While groups themselves are not indexed attributes, NCache requires query indexing on the target class to efficiently execute delete queries that use the $Group$ keyword.

Can I combine the group criteria with other standard SQL filters?

Yes, NCache allows combining the $Group$ keyword with other WHERE clause conditions, provided those attributes are query-indexed.

Delete Group Data in Cache with SQL Query [Deprecated]

NCache provides object queries similar to SQL that allow you to delete cache items based on specified criteria, making it useful for cache cleanup and logical data partition management. To support this, NCache extends standard SQL syntax with the special $Group$ keyword, which enables you to target and delete all cache items belonging to a specific group or set of groups. The delete operation is executed using ExecuteNonQuery, which processes the query entirely on the server to minimize network overhead and returns the number of cache items removed. To use this approach, cached objects must be query-indexed and associated with groups in advance.

Prerequisites

Before deleting group data in cache using the SQL Query, ensure that the following prerequisites are fulfilled:

To learn about the standard prerequisites required to work with all NCache client-side features, please refer to the given page on Client-Side API Prerequisites.
Custom classes/searchable attributes must be indexed as explained in Configuring Query Indexes.
For API details, refer to: ICache, ICacheReader, QueryCommand, ExecuteNonQuery, SearchService.

Delete Group

Given certain criteria, you can remove groups present in the cache with SQL queries. The following example removes all the data against a certain group through the SQL delete query.

// Precondition: Cache is already connected

// A user wants to remove the West Coast Customers Group as this region is no longer supported in their store
// Custom class is query indexed through the NCache Management Center or config.ncconf
// Create SQL Query with the specified criteria
// Make sure to use the Fully Qualified Name (FQN)
string query = "DELETE FROM FQN.Customer WHERE $Group$ = ?";

// Use QueryCommand for query execution
var queryCommand = new QueryCommand(query);

// Providing parameters for query
queryCommand.Parameters.Add("$Group$", "West Coast Customers");

// 'rowsAffected' number of items removed from cache
int rowsAffected = cache.SearchService.ExecuteNonQuery(queryCommand);

if (rowsAffected == 0)
{
    Console.WriteLine($"No West Coast Customers removed");
}

// Precondition: Cache is already connected

// A user wants to remove the West Coast Customers Group as this region is no longer supported in their store
// Custom class is query indexed through the NCache Management Center or config.ncconf
// Create SQL Query with the specified criteria
// Make sure to use the Fully Qualified Name (FQN)
String query = "DELETE FROM FQN.Customer WHERE $Group$ = ?";

// Use QueryCommand for query execution
QueryCommand queryCommand = new QueryCommand(query);

// Providing parameters for query
queryCommand.getParameters().put("$Group$", "West Coast Customers");

// 'rowsAffected' number of items removed from cache
int rowsAffected = cache.getSearchService().executeNonQuery(queryCommand);

if (rowsAffected == 0)
{
    System.out.println("No west coast customers removed");
}

# Precondition: Cache is already connected

# A user wants to remove the West Coast Customers Group as this region is no longer supported in their store
# Custom class is query indexed through the NCache Management Center or config.ncconf
# Create SQL Query with the specified criteria
# Make sure to use the Fully Qualified Name (FQN)
query = "DELETE FROM FQN.Customer WHERE $Group$ = ?"

# Use QueryCommand for query execution
query_command = QueryCommand(query)

# Provide parameters for the query
query_command.set_parameters({"$Group$": "West Coast Customers"})

# 'rowsAffected' number of items removed from cache
search_service = cache.get_search_service()
rows_affected = search_service.execute_non_query(query_command)

if rows_affected == 0:
    print(f" No West Coast Customers removed")

// Precondition: Cache is already connected
// This is an async method

// A user wants to remove the West Coast Customers Group as this region is no longer supported in their store
// Custom class is query indexed through the NCache Management Center or config.ncconf
// Create SQL Query with the specified criteria
// Make sure to use the Fully Qualified Name (FQN)
const query = "DELETE FROM FQN.Customer WHERE $Group$ = ?";

// Use QueryCommand for query execution
const queryCommand = new QueryCommand(query);

// Providing parameters for query
const parameters = new Map();
parameters.set("$Group$", "West Coast Customers");
queryCommand.setParameters(parameters);

// 'rowsAffected' number of items removed from cache
const searchService = await cache.getSearchService();
const rowsAffected = await searchService.executeNonQuery(queryCommand);

if (rowsAffected === 0) 
{
    console.log("No West Coast Customers removed");
}

Note

To ensure the operation is fail-safe, it is recommended to handle any potential exceptions within your application, as explained in Handling Failures.

For more details on object queries, please refer to the SQL Reference for NCache section.

Additional Resources

NCache provides sample application for Groups on GitHub.

Delete Group Data in Cache with SQL Query [Deprecated]

Prerequisites

Delete Group

Note

Additional Resources

See Also

Contact Us