update_data
Modify records in MSSQL database tables by specifying column updates and WHERE clause conditions to target specific data rows for changes.
Instructions
Updates data in an MSSQL Database table using a WHERE clause. The WHERE clause must be provided for security.
Input Schema
TableJSON 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'" |
Implementation Reference
- src/tools/UpdateDataTool.ts:27-63 (handler)The `run` method implements the core logic of the update_data tool: validates input, builds a parameterized UPDATE query, executes it on the MSSQL database, and returns success/error with rows affected.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)Input schema defining the structure and requirements for the tool's arguments: tableName (string), updates (object), whereClause (string).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:132-134 (registration)Dispatches calls to the update_data tool in the CallToolRequestSchema handler via switch statement.case updateDataTool.name: result = await updateDataTool.run(args); break;
- src/index.ts:116-118 (registration)Includes the updateDataTool instance in the list of available tools for ListToolsRequestSchema (non-readonly mode).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:89-89 (registration)Instantiates the UpdateDataTool class for use in the MCP server.const updateDataTool = new UpdateDataTool();