state-changing-data-manipulation

name

state-changing data manipulation

description

Use this skill when the user wants SQL data that goes beyond typical READs to encompass data modification loops like INSERT, UPDATE, or DELETE, as well as complex transactional logic for data lifecycles (merging records, setting TTL expirations, or updating locking flags). Trigger it for requests like “make examples that change the database”, “I want the model to delete stale records”, “consolidate these duplicate rows”, or “set an expiration deadline for the specified rows.”

Skill: state-changing data manipulation

1. Capability Definition & Real Case

• Professional Definition: The ability to translate non-retrieval modification intent into correct Data Manipulation Language (DML) statements (INSERT, UPDATE, DELETE). This entails grounding specific values to relational tables while maintaining schema constraints, executing lifecycle governance (TTL updates, immutability locks), and safely bridging semantic consolidation requests (record merging and atomic record splitting).
• Dimension Hierarchy: Query Reasoning->Transactional Logic->state-changing data manipulation

Real Case

[Case 1]

• Initial Environment: A corporate HR database with an 'employees' table including columns for 'email', 'salary', 'department_id', and 'hire_date'. The agent holds transactional write privileges.
• Real Question: Update the records of all employees in department 10 to reflect a 5% salary increase if they were hired before 2020.
• Real Trajectory: Identify the 'employees' table as the modification target. Verify columns 'salary', 'department_id', and 'hire_date'. Formulate the UPDATE statement with the correct arithmetic (salary * 1.05) and apply conditional filters to ensure updates apply only to specific historical bounds.
• Real Answer: UPDATE employees SET salary = salary * 1.05 WHERE department_id = 10 AND hire_date < '2020-01-01';
• Why this demonstrates the capability: This case tests translating dynamic conditional logic into a state-changing DML statement. It requires identifying that no data retrieval is requested, but rather a modification of existing values based on two specific logical filters, demonstrating active database payload management.

---

[Case 2]

• Initial Environment: A cloud monitoring platform stores 'system_alerts' with an 'expires_at' timestamp column and a 'status' column. It mandates active lifecycle grooming via SQL commands.
• Real Question: Set all resolved alerts to automatically expire by the end of the week.
• Real Trajectory: Ground the 'system_alerts' table. Filter for rows where status is 'resolved'. Calculate the absolute timestamp for the 'end of the week'. Update the 'expires_at' column to that future timestamp, establishing a TTL metadata lock.
• Real Answer: UPDATE system_alerts SET expires_at = '2025-10-05T23:59:59' WHERE status = 'resolved';
• Why this demonstrates the capability: This tests managing the temporal lifecycle of data via DML. The agent accurately transforms an abstract timeline constraint into a permanent database update, enforcing structural policy through the SQL UPDATE operator rather than simple record deletion.

---

[Case 3]

• Initial Environment: A CRM contains a 'client_notes' table without strict deduplication, meaning fragmented duplicate records occasionally appear representing the exact same project subject.
• Real Question: Combine the duplicate Project Alpha notes into a single main record and get rid of the extra one.
• Real Trajectory: Search 'client_notes' for 'Project Alpha' and identify fragmented IDs 101 and 102. Formulate an UPDATE using nested queries to concatenate the text into ID 101. Formulate a DELETE bound to ID 102 to finalize the semantic merge process.
• Real Answer: UPDATE client_notes SET content = (SELECT group_concat(content, ' | ') FROM client_notes WHERE subject = 'Project Alpha') WHERE id = 101; DELETE FROM client_notes WHERE id = 102;
• Why this demonstrates the capability: This illustrates semantic consolidation using transactional data manipulation statements explicitly. The agent must perform multi-step modifications to unify information from two Relational rows into one, maintaining record atomicity and actively scrubbing the legacy rows.

Pipeline Execution Instructions

To synthesize data for this capability, you must strictly follow a 3-phase pipeline. Do not hallucinate steps. Read the corresponding reference file for each phase sequentially:

1. Phase 1: Environment Exploration Read the exploration guidelines to discover raw knowledge seeds: references/EXPLORATION.md

2. Phase 2: Trajectory Selection Once Phase 1 is complete, read the selection criteria to evaluate the trajectory: references/SELECTION.md

3. Phase 3: Data Synthesis Once a trajectory passes Phase 2, read the synthesis instructions to generate the final data: references/SYNTHESIS.md