Class Update

Namespace: YndigoBlue.Velocity.Model

Assembly: YndigoBlue.Velocity.dll

Represents an UPDATE statement for modifying existing rows in a database table.

public class Update : Record

Inheritance: object

Datum

Record

Update

Examples

Basic UPDATE with WHERE clause:

using (Manager manager = new Manager(connection))
{
    manager.LoadSchema("my_schema");
    Schema schema = manager.GetSchema("my_schema");
    Table usersTable = schema["users"];

    // Update user's email
    Update update = new Update(usersTable);
    update.SetFieldString("email", "newemail@example.com");
    update.SetFieldDateTime("updated_at", DateTime.UtcNow);

    // Only update the specific user
    update.AddWhereClause(
        new Filter(new Criterion<int>(usersTable["id"], 12345))
    );

    int rowsUpdated = manager.Update(update);
    Console.WriteLine($"Updated {rowsUpdated} rows");
}

// SQL: UPDATE users
//      SET email = 'newemail@example.com', updated_at = '2025-01-07 10:30:00'
//      WHERE id = 12345

UPDATE with multiple field changes:

Update update = new Update(usersTable);
update.SetFieldString("status", "active");
update.SetFieldInt("login_count", 5);
update.SetFieldDateTime("last_login", DateTime.UtcNow);
update.SetFieldString("last_ip", "192.168.1.100");

update.AddWhereClause(
    new Criterion<string>(usersTable["username"], "john_doe")
);

int rowsUpdated = manager.Update(update);

// SQL: UPDATE users
//      SET status = 'active',
//          login_count = 5,
//          last_login = '2025-01-07 10:30:00',
//          last_ip = '192.168.1.100'
//      WHERE username = 'john_doe'

UPDATE with complex WHERE conditions:

// Update all inactive users who haven't logged in for 30 days
Update update = new Update(usersTable);
update.SetFieldString("status", "archived");
update.SetFieldDateTime("archived_at", DateTime.UtcNow);

Filter filter = new Filter();
filter.Add(new Criterion<string>(usersTable["status"], "inactive"));
filter.Add(new BooleanItem(BooleanType.And));
filter.Add(new Criterion<DateTime>(
    usersTable["last_login"],
    ConditionalType.LessThan,
    DateTime.UtcNow.AddDays(-30)
));

update.AddWhereClause(filter);
int rowsUpdated = manager.Update(update);

// SQL: UPDATE users
//      SET status = 'archived', archived_at = '2025-01-07 10:30:00'
//      WHERE status = 'inactive' AND last_login < '2024-12-08'

UPDATE using constructor with filter:

// Shorthand for creating update with WHERE clause
Criterion<int> criterion = new Criterion<int>(
    usersTable["id"],
    12345
);

Update update = new Update(usersTable, criterion);
update.SetFieldString("email", "updated@example.com");

int rowsUpdated = manager.Update(update);

// SQL: UPDATE users SET email = 'updated@example.com' WHERE id = 12345

UPDATE within a transaction:

using (Manager manager = new Manager(connection))
{
    manager.LoadSchema("my_schema");
    Schema schema = manager.GetSchema("my_schema");

    manager.BeginTransaction();
    try
    {
        // Update user status
        Update updateUser = new Update(schema["users"]);
        updateUser.SetFieldString("status", "suspended");
        updateUser.AddWhereClause(
            new Criterion<int>(schema["users"]["id"], 12345)
        );
        manager.Update(updateUser);

        // Update related orders
        Update updateOrders = new Update(schema["orders"]);
        updateOrders.SetFieldString("status", "on_hold");
        updateOrders.AddWhereClause(
            new Criterion<int>(schema["orders"]["user_id"], 12345)
        );
        manager.Update(updateOrders);

        manager.CommitTransaction();
    }
    catch
    {
        manager.RollbackTransaction();
        throw;
    }
}

UPDATE using function for field value:

// Use database function for timestamp
Update update = new Update(usersTable);
update.SetFieldString("status", "active");
update.SetFieldAsFunction("updated_at", new Function(FunctionType.Now));

update.AddWhereClause(
    new Criterion<int>(usersTable["id"], 12345)
);

manager.Update(update);

// SQL: UPDATE users SET status = 'active', updated_at = NOW() WHERE id = 12345

Bulk UPDATE with IN clause:

// Update multiple users at once
List<int> userIds = new List<int> { 1, 2, 3, 4, 5 };

Update update = new Update(usersTable);
update.SetFieldString("status", "verified");
update.SetFieldDateTime("verified_at", DateTime.UtcNow);

update.AddWhereClause(
    new Criterion<int>(
        usersTable["id"],
        ConditionalType.In,
        userIds
    )
);

int rowsUpdated = manager.Update(update);

// SQL: UPDATE users
//      SET status = 'verified', verified_at = '2025-01-07 10:30:00'
//      WHERE id IN (1, 2, 3, 4, 5)

Remarks

Update inherits from Record and combines field value setters with WHERE clause filtering. Use the inherited SetField* methods to specify new values, and add a WHERE clause to control which rows are updated.

UPDATE statements are executed using Engine.Manager.Update(Update). The number of affected rows is returned by the Manager.

WARNING: UPDATE without a WHERE clause modifies ALL rows in the table. Always double-check your WHERE conditions before executing.

Constructors

Update(Table): Creates a new UPDATE statement for the specified table without a WHERE clause.

Update(Table, IEnumerable<IFilterItem>): Creates a new UPDATE statement with multiple filter items.

Update(Table, IFilterItem): Creates a new UPDATE statement with a single filter item.

Update(Table, Filter): Creates a new UPDATE statement with a WHERE clause filter.

Properties

HasWhereClause: Gets whether this UPDATE statement has a WHERE clause.

WhereClause: Gets the WHERE clause filter that specifies which rows to update.

Methods

AddWhereClause(IEnumerable<IFilterItem>): Adds or replaces the WHERE clause using multiple filter items.

AddWhereClause(IFilterItem): Adds or replaces the WHERE clause using a single filter item.