TUpdateSqlStatement

Overview

The TUpdateSqlStatement node represents UPDATE statements in SQL. It handles table updates with SET clauses, WHERE conditions, and optional FROM clauses for multi-table updates.

SQL to AST Mapping

Simple UPDATE

UPDATE employees 
SET salary = salary * 1.1, last_updated = GETDATE()
WHERE department = 'Engineering';

AST Structure:

TUpdateSqlStatement
├── targetTable: TTable (employees)
├── resultColumnList: TResultColumnList (SET clause)
│   ├── TResultColumn (salary = salary * 1.1)
│   └── TResultColumn (last_updated = GETDATE())
└── whereClause: TWhereClause (department = 'Engineering')

UPDATE with FROM clause (SQL Server)

UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1 ON (dbo.Table2.ColA = dbo.Table1.ColA);

AST Structure:

TUpdateSqlStatement
├── targetTable: TTable (dbo.Table2)
├── resultColumnList: TResultColumnList (SET clause)
│   └── TResultColumn (dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB)
└── joins: TJoinList
    ├── TJoin (dbo.Table2)
    └── TJoin (INNER JOIN dbo.Table1 ON ...)

UPDATE with subquery

UPDATE products 
SET price = (SELECT AVG(price) FROM products WHERE category = p.category)
WHERE product_id = 123;

AST Structure:

TUpdateSqlStatement
├── targetTable: TTable (products)
├── resultColumnList: TResultColumnList
│   └── TResultColumn (price = subquery)
│       └── expr: TExpression (subquery_t)
│           └── subQuery: TSelectSqlStatement
└── whereClause: TWhereClause (product_id = 123)

Key Properties

Property	Type	Description	Access Method
targetTable	TTable	Target table for update	getTargetTable()
resultColumnList	TResultColumnList	SET clause assignments	getResultColumnList()
whereClause	TWhereClause	Filter conditions	getWhereClause()
joins	TJoinList	FROM clause tables	getJoins()
updateToken	TSourceToken	UPDATE keyword	getUpdateToken()
topClause	TTopClause	TOP clause (SQL Server)	getTopClause()
outputClause	TOutputClause	OUTPUT clause (SQL Server)	getOutputClause()

Common Usage Patterns

Analyzing UPDATE Assignments

public void analyzeUpdateStatement(TUpdateSqlStatement update) {
    System.out.println("Target table: " + update.getTargetTable().getFullName());

    // Analyze SET clause
    TResultColumnList setClause = update.getResultColumnList();
    if (setClause != null) {
        System.out.println("SET assignments:");
        for (int i = 0; i < setClause.size(); i++) {
            TResultColumn assignment = setClause.getResultColumn(i);
            TExpression expr = assignment.getExpr();

            // Get column being updated
            String columnName = getColumnName(assignment);
            System.out.println("  " + columnName + " = " + expr.toString());
        }
    }

    // Check WHERE clause
    if (update.getWhereClause() != null) {
        System.out.println("WHERE condition: " + 
            update.getWhereClause().getCondition().toString());
    } else {
        System.out.println("WARNING: No WHERE clause - affects all rows!");
    }
}

Extracting Updated Columns

public Set<String> getUpdatedColumns(TUpdateSqlStatement update) {
    Set<String> columns = new HashSet<>();

    TResultColumnList setClause = update.getResultColumnList();
    if (setClause != null) {
        for (int i = 0; i < setClause.size(); i++) {
            TResultColumn assignment = setClause.getResultColumn(i);

            // Extract column name from assignment
            if (assignment.getAliasClause() != null) {
                columns.add(assignment.getAliasClause().toString());
            } else {
                // Parse from expression if no explicit alias
                String columnName = extractColumnFromAssignment(assignment.getExpr());
                if (columnName != null) {
                    columns.add(columnName);
                }
            }
        }
    }

    return columns;
}

Multi-table Update Analysis

public void analyzeMultiTableUpdate(TUpdateSqlStatement update) {
    // Check if this is a multi-table update
    if (update.getJoins().size() > 0) {
        System.out.println("Multi-table update detected");

        // Target table
        System.out.println("Target: " + update.getTargetTable().getFullName());

        // Additional tables in FROM clause
        System.out.println("Additional tables:");
        for (int i = 0; i < update.getJoins().size(); i++) {
            TJoin join = update.getJoins().getJoin(i);
            TTable table = join.getTable();
            if (table != null) {
                System.out.println("  " + table.getFullName());

                // Analyze join conditions
                if (join.getJoinItems() != null) {
                    for (int j = 0; j < join.getJoinItems().size(); j++) {
                        TJoinItem joinItem = join.getJoinItems().getJoinItem(j);
                        if (joinItem.getOnCondition() != null) {
                            System.out.println("    ON: " + 
                                joinItem.getOnCondition().toString());
                        }
                    }
                }
            }
        }
    }
}

Dependency Analysis

public Map<String, Set<String>> analyzeUpdateDependencies(TUpdateSqlStatement update) {
    Map<String, Set<String>> dependencies = new HashMap<>();

    TResultColumnList setClause = update.getResultColumnList();
    if (setClause != null) {
        for (int i = 0; i < setClause.size(); i++) {
            TResultColumn assignment = setClause.getResultColumn(i);
            TExpression expr = assignment.getExpr();

            String targetColumn = getColumnName(assignment);
            Set<String> referencedColumns = findReferencedColumns(expr);

            dependencies.put(targetColumn, referencedColumns);
        }
    }

    return dependencies;
}

private Set<String> findReferencedColumns(TExpression expr) {
    Set<String> columns = new HashSet<>();

    expr.postOrderTraverse(new IExpressionVisitor() {
        public boolean exprVisit(TParseTreeNode node, boolean isLeafNode) {
            if (node instanceof TExpression) {
                TExpression e = (TExpression) node;
                if (e.getExpressionType() == EExpressionType.simple_object_name_t) {
                    columns.add(e.getObjectOperand().toString());
                }
            }
            return true;
        }
    });

    return columns;
}

Database-Specific Features

SQL Server

-- TOP clause
UPDATE TOP (10) employees 
SET salary = salary * 1.1;

-- OUTPUT clause
UPDATE employees 
SET salary = salary * 1.1
OUTPUT DELETED.salary, INSERTED.salary
WHERE department = 'Sales';

-- FROM clause with joins
UPDATE e
SET salary = salary * 1.1
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE d.name = 'Engineering';

Access via: - getTopClause() - TOP clause - getOutputClause() - OUTPUT clause - getJoins() - FROM clause tables

MySQL

-- Multi-table UPDATE
UPDATE employees e, departments d
SET e.salary = e.salary * 1.1
WHERE e.dept_id = d.id AND d.name = 'Engineering';

-- LIMIT clause
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = 'Sales'
LIMIT 10;

-- ORDER BY with LIMIT
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = 'Sales'
ORDER BY hire_date
LIMIT 10;

Access via: - getLimitClause() - LIMIT clause - getOrderByClause() - ORDER BY clause

PostgreSQL

-- FROM clause
UPDATE employees 
SET salary = salary * d.multiplier
FROM departments d
WHERE employees.dept_id = d.id;

-- RETURNING clause
UPDATE employees 
SET salary = salary * 1.1
WHERE department = 'Engineering'
RETURNING id, name, salary;

Access via: - getJoins() - FROM clause tables - getReturningClause() - RETURNING clause

Oracle

-- Error logging
UPDATE employees 
SET salary = salary * 1.1
WHERE department = 'Engineering'
LOG ERRORS INTO error_table;

Access via: - getErrorLoggingClause() - Error logging clause

Couchbase

-- USE KEYS/USE INDEX
UPDATE users 
USE KEYS "user123"
SET status = 'active';

-- UNSET clause
UPDATE users 
SET name = 'John'
UNSET temp_field
WHERE id = 123;

Access via: - getUseKeyIndex() - USE KEYS/INDEX clause - getUnSetTerms() - UNSET clause

Performance Considerations

Update Impact Analysis

public void analyzeUpdateImpact(TUpdateSqlStatement update) {
    // Check for WHERE clause
    if (update.getWhereClause() == null) {
        System.out.println("WARNING: Full table update - no WHERE clause");
    }

    // Analyze updated columns for index impact
    Set<String> updatedColumns = getUpdatedColumns(update);
    System.out.println("Updated columns: " + updatedColumns);

    // Check for complex expressions in SET clause
    TResultColumnList setClause = update.getResultColumnList();
    if (setClause != null) {
        for (int i = 0; i < setClause.size(); i++) {
            TResultColumn assignment = setClause.getResultColumn(i);
            int complexity = calculateExpressionComplexity(assignment.getExpr());
            if (complexity > 5) {
                System.out.println("Complex assignment detected: " + 
                    assignment.getExpr().toString());
            }
        }
    }
}

Join Optimization Analysis

public void analyzeJoinPerformance(TUpdateSqlStatement update) {
    if (update.getJoins().size() > 0) {
        System.out.println("Multi-table update performance considerations:");

        // Check for proper join conditions
        for (int i = 0; i < update.getJoins().size(); i++) {
            TJoin join = update.getJoins().getJoin(i);
            if (join.getJoinItems() == null || join.getJoinItems().size() == 0) {
                System.out.println("WARNING: Cartesian product detected");
            }
        }

        // Suggest index usage
        analyzeIndexUsage(update);
    }
}

See Also

• TResultColumnList - SET clause structure
• TWhereClause - WHERE conditions
• TJoinList - FROM clause joins
• TTable - Target table references
• SQL Statements - Other statement types