update_data
Modifies records in an MSSQL Database table by specifying updates with a required WHERE clause for precise and secure data changes. Ideal for targeted database management tasks.
Instructions
Updates data in an MSSQL Database table using a WHERE clause. The WHERE clause must be provided for security.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| tableName | Yes | Name of the table to update | |
| updates | Yes | Key-value pairs of columns to update. Example: { 'status': 'active', 'last_updated': '2025-01-01' } | |
| whereClause | Yes | WHERE clause to identify which records to update. Example: "genre = 'comedy' AND created_date <= '2025-07-05'" |
Input Schema (JSON Schema)
{
"properties": {
"tableName": {
"description": "Name of the table to update",
"type": "string"
},
"updates": {
"description": "Key-value pairs of columns to update. Example: { 'status': 'active', 'last_updated': '2025-01-01' }",
"type": "object"
},
"whereClause": {
"description": "WHERE clause to identify which records to update. Example: \"genre = 'comedy' AND created_date <= '2025-07-05'\"",
"type": "string"
}
},
"required": [
"tableName",
"updates",
"whereClause"
],
"type": "object"
}
Implementation Reference
- src/tools/UpdateDataTool.ts:27-63 (handler)The main handler function `run` that implements the core logic of the `update_data` tool, handling SQL UPDATE queries with parameterization for security.async run(params: any) { let query: string | undefined; try { const { tableName, updates, whereClause } = params; // Basic validation: ensure whereClause is not empty if (!whereClause || whereClause.trim() === '') { throw new Error("WHERE clause is required for security reasons"); } const request = new sql.Request(); // Build SET clause with parameterized queries for security const setClause = Object.keys(updates) .map((key, index) => { const paramName = `update_${index}`; request.input(paramName, updates[key]); return `[${key}] = @${paramName}`; }) .join(", "); query = `UPDATE ${tableName} SET ${setClause} WHERE ${whereClause}`; const result = await request.query(query); return { success: true, message: `Update completed successfully. ${result.rowsAffected[0]} row(s) affected`, rowsAffected: result.rowsAffected[0], }; } catch (error) { console.error("Error updating data:", error); return { success: false, message: `Failed to update data ${query ? ` with '${query}'` : ''}: ${error}`, }; } }
- src/tools/UpdateDataTool.ts:8-25 (schema)The inputSchema defining the parameters for the `update_data` tool: tableName, updates object, and required whereClause.inputSchema = { type: "object", properties: { tableName: { type: "string", description: "Name of the table to update" }, updates: { type: "object", description: "Key-value pairs of columns to update. Example: { 'status': 'active', 'last_updated': '2025-01-01' }", }, whereClause: { type: "string", description: "WHERE clause to identify which records to update. Example: \"genre = 'comedy' AND created_date <= '2025-07-05'\"" }, }, required: ["tableName", "updates", "whereClause"], } as any;
- src/index.ts:83-83 (registration)Instantiation of the UpdateDataTool instance used throughout the server.const updateDataTool = new UpdateDataTool();
- src/index.ts:109-113 (registration)Registration of the `update_data` tool (via updateDataTool instance) in the ListToolsRequestSchema handler, making it discoverable.server.setRequestHandler(ListToolsRequestSchema, async () => ({ tools: isReadOnly ? [listTableTool, readDataTool, describeTableTool] // todo: add searchDataTool to the list of tools available in readonly mode once implemented : [insertDataTool, readDataTool, describeTableTool, updateDataTool, createTableTool, createIndexTool, dropTableTool, listTableTool], // add all new tools here }));
- src/index.ts:126-127 (registration)Dispatch/registration in the CallToolRequestSchema switch statement, routing calls to `update_data` to its run method.case updateDataTool.name: result = await updateDataTool.run(args);