9.2.0

Inserts, Updates, and Deletes

The following methods all have the same return value:

{
    "result": "Value of the `result` parameter to `queryExecute`",
    "query": "Return value of running `queryExecute` - a CFML query object"
}

insert, update, and delete actions always return a query object for query, regardless of your configured returnFormat.

insert

This call must come after setting the query's table using from or table.

You can insert a single record by passing a struct:

QueryBuilder
query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "age" = 55
    } );
MySQL
INSERT INTO `users` (`age`, `email`, `name`)
VALUES (?, ?, ?)

You can specify any query param options such as the SQL type by passing a struct with the parameters you would pass to cfqueryparam.

QueryBuilder
query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "age" = { value = 55, cfsqltype = "CF_SQL_INTEGER" }
    } );
MySQL
INSERT INTO `users` (`age`, `email`, `name`)
VALUES (?, ?, ?)

Raw values can be supplied to an insert statement.

QueryBuilder
query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "robert@test.com",
        "updatedDate" = query.raw( "NOW()" )
    } );
MySQL
INSERT INTO `users` (`age`, `email`, `updatedDate`)
VALUES (?, ?, NOW())

Multiple rows can be inserted in a batch by passing an array of structs to insert.

This is not the same as looping over and array and calling insert in the loop. Using an array with insert will batch the inserts in one SQL call. Looping over an array and calling insert each time will create a SQL request for each item in the array. Bottom line, pass your array to insert!

QueryBuilder
query.from( "users" ).insert( [
    { "email" = "john@example.com", "name" = "John Doe" },
    { "email" = "jane@example.com", "name" = "Jane Doe" }
] );
INSERT INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)

insertIgnore

This call must come after setting the query's table using from or table.

Inserts data into a table while ignoring duplicate key conflicts.

target is only required for SQLServerGrammar and OracleGrammar

QueryBuilder
query.from( "users" )
    .insertIgnore(
        values = [
            { "email" = "foo", "name" = "bar" },
            { "email" = "baz", "name" = "bam" }
        ],
        target = [ "email" ]
    );
INSERT IGNORE INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)

insertUsing

This call must come after setting the query's table using from or table.

Inserts data into a table using a subquery as the source.

qb.from( "users" )
    .insertUsing( function( q ) {
        q.from( "activeDirectoryUsers" )
            .select( [ "email", "modifiedDate AS createdDate" ] )
            .where( "active", 1 );
    } );
INSERT INTO `users` (`email`, `createdDate`)
SELECT `email`, `modifiedDate` AS `createdDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?

You can also pass in an array of column names to avoid aliasing in your source query.

qb.from( "users" )
    .insertUsing(
        columns = [ "email", "createdDate" ],
        source = function( q ) {
            q.from( "activeDirectoryUsers" )
                 .select( [ "email", "modifiedDate" ] )
                 .where( "active", 1 );
        }
    );
INSERT INTO `users` (`email`, `createdDate`)
SELECT `email`, `modifiedDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?

Alternatively, the source can be defined as a QueryBuilder object:

qb.from( "users" )
    .insertUsing(
        qb.newQuery()
            .from( "activeDirectoryUsers" )
            .select( [ "email", "modifiedDate AS createdDate" ] )
            .where( "active", 1 )
    );
INSERT INTO `users` (`email`, `createdDate`)
SELECT `email`, `modifiedDate` AS `createdDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?

update

This call must come after setting the query's table using from or table.

Updates a table with a struct of column and value pairs.

QueryBuilder
query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );
MySQL
UPDATE `users`
SET `email` = ?,
    `name` = ?

You can specify any query param options such as the SQL type by passing a struct with the parameters you would pass to cfqueryparam.

QueryBuilder
query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar",
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
    } );
MySQL
UPDATE `users`
SET `email` = ?,
    `name` = ?,
    `updatedDate` = ?

Any constraining of the update query should be done using the appropriate WHERE statement before calling update.

QueryBuilder
query.from( "users" )
    .whereId( 1 )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );
MySQL
UPDATE `users`
SET `email` = ?,
    `name` = ?
WHERE `Id` = ?

You can update a column based on another column using a raw expression.

QueryBuilder
query.from( "hits" )
    .where( "page", "someUrl" )
    .update( {
        "count" = query.raw( "count + 1" )
    } );
MySQL
UPDATE `hits`
SET `count` = count + 1
WHERE `page` = ?

Updating Null values

Null values can be inserted by using queryparam syntax:

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = "", null=true },
		} )

if you are using full null support the following (easier) syntax is also allowed:

query.from("user")
		.whereId( 10 )
		.update( {
			manager_FK = { value = null },
		} )

Updating with Subselects

Subselects can be used to update values by passing a closure as the value

qb.table( "employees" )
    .update( {
		    "departmentName" = function( q ) {
		        q.from( "departments" )
		            .select( "name" )
		            .whereColumn( "employees.departmentId", "departments.id" );
		    } )
		} );
UPDATE `employees`
SET `departmentName` = (
    SELECT `name`
    FROM `departments`
    WHERE `employees`.`departmentId` = `departments`.`id`
)

You can also pass a builder instance in place of the closure.

qb.table( "employees" )
    .update( {
		    "departmentName" = qb.newQuery()
		        .from( "departments" )
		        .select( "name" )
		        .whereColumn( "employees.departmentId", "departments.id" )
		    } )
		} );

Updating with Joins

qb will correctly format JOIN clauses in your UPDATE statements for your database grammar.

OracleGrammar does not support JOIN clauses inUPDATE statements. Consider using subselects in your UPDATE statement instead.

qb.table( "employees" )
    .join( "departments", "departments.id", "employees.departmentId" )
    .update( {
        "employees.departmentName": qb.raw( "departments.name" )
    } );
UPDATE `employees`
INNER JOIN `departments`
    ON `departments`.`id` = `employees`.`departmentId`
SET `employees`.`departmentName` = departments.name

addUpdate

Adds values to a later update, similar to addSelect.

QueryBuilder
query.from( "users" )
    .whereId( 1 )
    .addUpdate( {
        "email" = "foo",
        "name" = "bar"
    } )
    .when( true, function( q ) {
        q.addUpdate( {
            "foo": "yes"
        } );
    } )
    .when( false, function( q ) {
        q.addUpdate( {
            "bar": "no"
        } );
    } )
    .update();
MySQL
UPDATE `users`
SET `email` = ?,
    `foo` = ?,
    `name` = ?
WHERE `Id` = ?

updateOrInsert

Performs an update statement if the configured query returns true for exists. Otherwise, performs an insert statement.

If an update statement is performed qb applies a limit( 1 ) to the update statement.

QueryBuilder
query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );
MySQL
UPDATE `users`
SET `email` = ?,
    `name` = ?
WHERE `email` = ?
LIMIT 1

If the configured query returns 0 records, then an insert statement is performed.

QueryBuilder
query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );
MySQL
INSERT INTO `users` (`email`, `name`)
VALUES (?, ?)

upsert

An upsert is a batch operation that either inserts or updates a row depending on if a target match is found. If a row is matched with the target column(s), then the matched row is updated. Otherwise a new row is inserted.

In most database grammars, the target columns are required to be primary key or unique indexes.

qb.table( "users" )
    .upsert(
        values = [
            {
                "username": "johndoe",
                "active": 1,
                "createdDate": "2021-09-08 12:00:00",
                "modifiedDate": "2021-09-08 12:00:00"
            },
            {
                "username": "janedoe",
                "active": 1,
                "createdDate": "2021-09-10 10:42:13",
                "modifiedDate": "2021-09-10 10:42:13"
            },
        ],
        target = [ "username" ],
        update = [ "active", "modifiedDate" ],
    );
INSERT INTO `users`
    (`active`, `createdDate`, `modifiedDate`, `username`)
VALUES
    (?, ?, ?, ?),
    (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

The update clause in a upsert can also accept raw values, making it very useful for tracking data like statistics.

qb.table( "stats" )
    .upsert(
        values = [
            { "postId": 1, "viewedDate": "2021-09-08", "views": 1 },
            { "postId": 2, "viewedDate": "2021-09-08", "views": 1 }
        ],
        target = [ "postId", "viewedDate" ],
        update = { "views": qb.raw( "stats.views + 1" ) }
    );
INSERT INTO `stats`
    (`postId`, `viewedDate`, `views`)
VALUES
    (?, ?, ?),
    (?, ?, ?)
ON DUPLICATE KEY UPDATE
    `views` = stats.views + 1

A source callback or QueryBuilder instance can be used instead of explicit values. This allows you to do upserts across tables or subqueries.

To do this, provide a source that is either a function to configure a new QueryBuilder instance or an already configured QueryBuilder instance. Then specify the columns that will be affected as an array of strings to values.

qb.table( "stats" )
    .upsert(
        source = function( q ) {
            q.from( "activeDirectoryUsers" )
                .select( [
                    "username",
                    "active",
                    "createdDate",
                    "modifiedDate"
                ] );
        },
        values = [ "username", "active", "createdDate", "modifiedDate" ],
        target = [ "username" ],
        update = [ "active", "modifiedDate" ]
    );
INSERT INTO `users`
    (`username`, `active`, `createdDate`, `modifiedDate`)
SELECT `username`, `active`, `createdDate`, `modifiedDate`
FROM `activeDirectoryUsers`
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

delete

Deletes all records that the query returns.

QueryBuilder
query.from( "users" )
    .where( "email", "foo" )
    .delete();
MySQL
DELETE FROM `users`
WHERE `email` = ?

The id argument is a convenience to delete a single record by id.

QueryBuilder
query.from( "users" )
    .delete( 1 );
MySQL
DELETE FROM `users`
WHERE `id` = ?

returning

returning is only supported in PostgresGrammar, SqlServerGrammar, and SQLiteGrammar. Using this method on unsupported grammars will result in an UnsupportedOperation exception. Be aware that using this method constrains your grammar choices.

Specifies columns to be returned from the insert query.

QueryBuilder
query.from( "users" )
    .returning( "id" )
    .insert( {
        "email" = "foo",
        "name" = "bar"
    } );
INSERT INTO [users] ([email], [name])
OUTPUT INSERTED.[id]
VALUES (?, ?)

The returning function also applies to update and delete calls.

QueryBuilder
query.table( "users" )
    .returning( [ "id", "modifiedDate" ] )
    .where( "id", 1 )
    .update( { "email": "john@example.com" } );
UPDATE [users]
SET [email] = ?
OUTPUT INSERTED.[id], INSERTED.[modifiedDate]
WHERE [id] = ?
QueryBuilder
query.table( "users" )
    .returning( "id" )
    .where( "active", 0 )
    .delete();
DELETE FROM [users]
OUTPUT DELETED.[id]
WHERE [active] = ?

You can also use raw Expressions in a returning call. This is especially useful for SQL Server returning both the old and new values from an update call.

QueryBuilder
qb.from( "users" )
    .where( "id", 1 )
    .returningRaw( [
        "DELETED.modifiedDate AS oldModifiedDate",
        "INSERTED.modifiedDate AS newModifiedDate"
    ] )
    .update( { "email": "john@example.com" } );
UPDATE [users]
SET [email] = ?
OUTPUT
    DELETED.modifiedDate AS oldModifiedDate,
    INSERTED.modifiedDate AS newModifiedDate
WHERE [id] = ?
PreviousAggregatesNextOptions and Utilities

Last updated