update_data
Modify specific records in Microsoft SQL Server tables by applying column updates to rows matching a WHERE clause condition for targeted data management.
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 main handler function that performs the SQL UPDATE operation using parameterized queries for security, validates the WHERE clause, and returns success/failure 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 required parameters: tableName (string), updates (object of column-value pairs), whereClause (string for security).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:109-113 (registration)Registers the updateDataTool instance in the list of available tools returned by ListToolsRequest (in non-readonly mode).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)Dispatches calls to the update_data tool by invoking its run method in the CallToolRequest handler switch statement.case updateDataTool.name: result = await updateDataTool.run(args);
- src/index.ts:83-83 (registration)Instantiates the UpdateDataTool class for use in the MCP server.const updateDataTool = new UpdateDataTool();