Using Query API to delete data

You can use the QueryRequest API and delete one or more rows from a NoSQL table that satisfy a filter condition.

You can use the DELETE SQL command in the Query request to delete data. To execute your query, you use the NoSQLHandle.query() API.

Download the full code ModifyData.java from the examples here.


//delete rows based on a filter condition
private static void deleteRows(NoSQLHandle handle, String sqlstmt) throws Exception {
   QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
   handle.query(queryRequest);
   System.out.println("Deleted row(s) from table " + tableName);
}


String del_stmt ="DELETE FROM stream_acct acct1 WHERE 
acct1.acct_data.firstName=\"Adelaide\" AND acct1.acct_data.lastName=\"Willard\"";
/*delete rows based on a filter condition*/
deleteRows(handle,del_stmt);

You can use the DELETE SQL command in the Query request to delete data. To execute your query use the borneo.NoSQLHandle.query() method.

Download the full code ModifyData.py from the examples here.


#del row(s) with a filter condition
def delete_rows(handle,sqlstmt):
   request = QueryRequest().set_statement(sqlstmt)
   result = handle.query(request)
   print('Deleted data from table: stream_acct')

# delete data based on a filter condition
del_stmt ='''DELETE FROM stream_acct acct1 WHERE 
acct1.acct_data.firstName="Adelaide" AND acct1.acct_data.lastName="Willard"'''
delete_rows(handle,del_stmt)

You can use the DELETE SQL command in the Query request to delete data. To execute a query use the Client.Query function.

Download the full code ModifyData.go from the examples here.


//delete rows based on a filter condition
func deleteRows(client *nosqldb.Client, err error, tableName string, querystmt string)(){
   prepReq := &nosqldb.PrepareRequest{
		Statement: querystmt,
   }
   prepRes, err := client.Prepare(prepReq)
   if err != nil {
      fmt.Printf("Prepare failed: %v\n", err)
      return
   }
   queryReq := &nosqldb.QueryRequest{
	PreparedStatement: &prepRes.PreparedStatement,   }
   var results []*types.MapValue
   for {
      queryRes, err := client.Query(queryReq)
      if err != nil {
	  fmt.Printf("Upsert failed: %v\n", err)
	  return
      }
      res, err := queryRes.GetResults()
      if err != nil {
         fmt.Printf("GetResults() failed: %v\n", err)
	  return
      }
      results = append(results, res...)
      if queryReq.IsDone() {
         break
      }
   }
   for i, r := range results {
	fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
   }
   fmt.Printf("Deleted data from the table: %v\n",tableName)
}

delete_stmt := `DELETE FROM stream_acct acct1 WHERE 
acct1.acct_data.firstName="Adelaide" AND acct1.acct_data.lastName="Willard"`
deleteRows(client, err,tableName,delete_stmt)

You can use the DELETE SQL command in the Query request to delete data. To execute a query use query method.

JavaScript: Download the full code ModifyData.js from the examples here.

/*deletes data based on a filter conditioin */
async function deleteRows(handle,querystmt) {
   const opt = {};
   try {
      do {
         const result = await handle.query(querystmt, opt);
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

await deleteRows(handle,del_stmt);
console.log("Rows deleted");

TypeScript: Download the full code ModifyData.ts from the examples here.

async function deleteRows(handle: NoSQLClient,querystmt: string) {
   const opt = {};
   try {
      do {
         const result = await handle.query<StreamInt>(querystmt, opt);
         for(let row of result.rows) {
            console.log('  %O', row);
         }
         opt.continuationKey = result.continuationKey;
      } while(opt.continuationKey);
   } catch(error) {
      console.error('  Error: ' + error.message);
   }
}

await deleteRows(handle,del_stmt);
console.log("Rows deleted");

You can use the DELETE SQL command in the Query request to delete data. To execute a query, you may call QueryAsync method or call GetQueryAsyncEnumerable method and iterate over the resulting async enumerable.

Download the full code ModifyData.cs from the examples here.

private static async Task deleteRows(NoSQLClient client,String querystmt){
   var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
}

await deleteRows(client,del_stmt);
Console.WriteLine("Rows removed from the table");