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

Name	Type	Required	Default	Description
values	struct | array<struct>	true		A struct or array of structs to insert in to the table.
options	struct	false	{}	Any additional queryExecute options.
toSQL	boolean	false	false	If true, returns the raw SQL string instead of running the query. Useful for debugging.

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" = "[e-mail]",
        "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" = "[e-mail]",
        "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" = "[e-mail]",
        "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" = "[e-mail]", "name" = "John Doe" },
    { "email" = "[e-mail]", "name" = "Jane Doe" }
] );

MySQL

INSERT INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)

Oracle

INSERT ALL
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
SELECT 1 FROM dual

insertIgnore

Name	Type	Required	Default	Description
values	struct | array<struct>	true		A struct or array of structs to insert in to the table.
target	array<string>	false	[]	An array of key column names to match on. (SQL Server and Oracle grammars only.)
options	struct	false	{}	Any additional queryExecute options.
toSQL	boolean	false	false	If true, returns the raw SQL string instead of running the query. Useful for debugging.

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" ]
    );

MySQL

INSERT IGNORE INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)

SQL Server

MERGE [users] AS [qb_target]
USING (VALUES (?, ?), (?, ?)) AS [qb_src] ([email], [name])
ON [qb_target].[email] = [qb_src].[email]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([email], [name]) VALUES ([email], [name]);

Postgres

INSERT INTO "users" ("email", "name")
VALUES (?, ?), (?, ?)
ON CONFLICT DO NOTHING

Oracle

MERGE INTO "USERS" "QB_TARGET"
USING (SELECT ?, ? FROM dual UNION ALL SELECT ?, ? FROM dual) "QB_SRC"
ON "QB_TARGET"."EMAIL" = "QB_SRC"."EMAIL"
WHEN NOT MATCHED THEN
INSERT ("EMAIL", "NAME")
VALUES ("QB_SRC"."EMAIL", "QB_SRC"."NAME")

insertUsing

Name	Type	Required	Default	Description
source	function | QueryBuilder	true		A callback or builder instance to serve as the source of the insert.
columns	array<string>	false		An array of column names that will be inserted. If no columns are passed, the columns will be derived from the source columns and aliases.
options	struct	false	{}	Any additional queryExecute options.
toSQL	boolean	false	false	If true, returns the raw SQL string instead of running the query. Useful for debugging.

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

Name	Type	Required	Default	Description
values	struct	false	{}	A struct of column and value pairs to update. These column and value pairs are appended to any already set with the addUpdate method.
options	struct	false	{}	Any additional queryExecute options.
toSQL	boolean	false	false	If true, returns the raw SQL string instead of running the query. Useful for debugging.

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" );
		    } )
		} );

MySQL

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" )
    } );

MySQL

UPDATE `employees`
INNER JOIN `departments`
    ON `departments`.`id` = `employees`.`departmentId`
SET `employees`.`departmentName` = departments.name

SQL Server

UPDATE [employees]
SET [employees].[departmentName] = departments.name
FROM [employees]
INNER JOIN [departments]
    ON [departments].[id] = [employees].[departmentId]

Postgres

UPDATE "employees"
SET "employees"."departmentName" = departments.name
FROM "departments"
WHERE "departments"."id" = "employees"."departmentId"

addUpdate

Name	Type	Required	Default	Description
values	struct	true		A struct of column and value pairs to add to the update clause.

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

Name	Type	Required	Default	Description
values	struct	true		A struct of column and value pairs to either update or insert.
options	boolean	false	{}	Any additional queryExecute options.
toSql	boolean	false	false	If true, returns the raw SQL string instead of running the query. Useful for debugging.

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

Name	Type	Required	Default	Description
values	struct | array<struct> | array<string>	true		A struct or array of structs to insert into or update on the table. If a source is provided, this should be an array of column names to update instead.
target	string | array<string>	true		A column name or array of column names to match the values to the table. If a match is found, the record will be updated. Otherwise, a new record will be inserted. Most database grammars required these columns to have either a primary key or a unique index.
update	array | struct	false	null	Either an array of columns to update using the current value matched or a struct containing the column names as keys and the corresponding to update. If blank, it will update all the columns in the passed in value.
source	function | QueryBuilder	false	null	A callback function or QueryBuilder object to use as the source for the upsert. When using this parameter, values must be an array of column names to update.
deleteUmatched	boolean	false	false	Boolean flag to delete any unmatched source records as part the upsert. (SQL Server only.)
options	boolean	false	{}	Any additional queryExecute options.
toSql	boolean	false	false	If true, returns the raw SQL string instead of running the query. Useful for debugging.

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" ],
    );

MySQL

INSERT INTO `users`
    (`active`, `createdDate`, `modifiedDate`, `username`)
VALUES
    (?, ?, ?, ?),
    (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

SQL Server

MERGE [users] AS [qb_target]
USING (VALUES (?, ?, ?, ?), (?, ?, ?, ?)) AS [qb_src]
    ([active], [createdDate], [modifiedDate], [username])
ON [qb_target].[username] = [qb_src].[username]
WHEN MATCHED THEN UPDATE
    SET [active] = [qb_src].[active],
        [modifiedDate] = [qb_src].[modifiedDate]
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([active], [createdDate], [modifiedDate], [username])
    VALUES
    ([active], [createdDate], [modifiedDate], [username])

Postgres

INSERT INTO "users"
    ("active", "createdDate", "modifiedDate", "username")
VALUES
    (?, ?, ?, ?),
    (? ,? ,? ,?)
ON CONFLICT ("username") DO UPDATE
    "active" = EXCLUDED."active",
    "modifiedDate" = EXCLUDED."modifiedDate"

Oracle

MERGE INTO "USERS" "QB_TARGET"
USING (
    SELECT ?, ?, ?, ? FROM dual
    UNION ALL
    SELECT ?, ?, ?, ? FROM dual
) "QB_SRC"
ON "QB_TARGET"."USERNAME" = "QB_SRC"."USERNAME"
WHEN MATCHED THEN UPDATE
    SET "ACTIVE" = "QB_SRC"."ACTIVE",
        "MODIFIEDDATE" = "QB_SRC"."MODIFIEDDATE"
WHEN NOT MATCHED THEN INSERT
    ("ACTIVE", "CREATEDDATE", "MODIFIEDDATE", "USERNAME")
    VALUES
    ("QB_SRC"."ACTIVE", "QB_SRC"."CREATEDDATE", "QB_SRC"."MODIFIEDDATE", "QB_SRC"."USERNAME")

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" ) }
    );

MySQL

INSERT INTO `stats`
    (`postId`, `viewedDate`, `views`)
VALUES
    (?, ?, ?),
    (?, ?, ?)
ON DUPLICATE KEY UPDATE
    `views` = stats.views + 1

SQL Server

MERGE [stats] AS [qb_target]
USING (VALUES (?, ?, ?), (?, ?, ?)) AS [qb_src]
    ([postId], [viewedDate], [views])
ON [qb_target].[postId] = [qb_src].[postId]
    AND [qb_target].[viewedDate] = [qb_src].[viewedDate]
WHEN MATCHED THEN UPDATE
    SET [views] = stats.views + 1
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([postId], [viewedDate], [views])
    VALUES
    ([postId], [viewedDate], [views])

Postgres

INSERT INTO "stats"
    ("postId", "viewedDate", "views")
VALUES
    (?, ?, ?),
    (?, ?, ?)
ON CONFLICT ("postId", "viewedDate") DO UPDATE
    "views" = stats.views + 1

Oracle

MERGE INTO "STATS" "QB_TARGET"
USING (
    SELECT ?, ?, ? FROM dual
    UNION ALL
    SELECT ?, ?, ? FROM dual
) "QB_SRC"
ON "QB_TARGET"."POSTID" = "QB_SRC"."POSTID"
    AND "QB_TARGET"."VIEWEDDATE" = "QB_SRC"."VIEWEDDATE"
WHEN MATCHED THEN UPDATE
    SET "VIEWS" = stats.views + 1
WHEN NOT MATCHED THEN INSERT
    ("POSTID", "VIEWEDDATE", "VIEWS")
    VALUES
    ("QB_SRC"."POSTID", "QB_SRC"."VIEWEDDATE", "QB_SRC"."VIEWS")

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" ]
    );

MySQL

INSERT INTO `users`
    (`username`, `active`, `createdDate`, `modifiedDate`)
SELECT `username`, `active`, `createdDate`, `modifiedDate`
FROM `activeDirectoryUsers`
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

SQL Server

MERGE [users] AS [qb_target]
USING (
    SELECT [username], [active], [createdDate], [modifiedDate]
    FROM [activeDirectoryUsers]
) AS [qb_src]
ON [qb_target].[username] = [qb_src].[username]
WHEN MATCHED THEN UPDATE
    SET [active] = [qb_src].[active],
        [modifiedDate] = [qb_src].[modifiedDate]
WHEN NOT MATCHED BY TARGET THEN INSERT
    ([username], [active], [createdDate], [modifiedDate])
VALUES ([username], [active], [createdDate], [modifiedDate]);

Postgres

INSERT INTO "users"
("username", "active", "createdDate", "modifiedDate")
SELECT "username", "active", "createdDate", "modifiedDate"
FROM "activeDirectoryUsers"
ON CONFLICT ("username") DO UPDATE
    "active" = EXCLUDED."active",
    "modifiedDate" = EXCLUDED."modifiedDate"

Oracle

MERGE INTO "USERS" "QB_TARGET"
USING (
    SELECT "USERNAME", "ACTIVE", "CREATEDADATE", "MODIFIEDDATE"
    FROM "ACTIVEDIRECTORYUSERS"
) "QB_SRC"
ON "QB_TARGET"."USERNAME" = "QB_SRC"."USERNAME"
WHEN MATCHED THEN UPDATE
    SET "ACTIVE" = "QB_SRC"."ACTIVE",
        "MODIFIEDDATE" = "QB_SRC"."MODIFIEDDATE"
WHEN NOT MATCHED THEN INSERT
    ("USERNAME", "ACTIVE", "CREATEDDATE", "MODIFIEDDATE")
    VALUES
    (
        "QB_SRC"."USERNAME",
        "QB_SRC"."ACTIVE",
        "QB_SRC"."CREATEDDATE",
        "QB_SRC"."MODIFIEDDATE"
    )

delete

Name	Type	Required	Default	Description
id	any	false		A convenience argument for `where( "id", "=", arguments.id ). The query can be constrained by normal WHERE methods as well.
idColumn	string	false	"id"	The name of the id column for the delete shorthand.
options	boolean	false	{}	Any additional queryExecute options.
toSql	boolean	false	false	If true, returns the raw SQL string instead of running the query. Useful for debugging.

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

Name	Type	Required	Default	Description
columns	string | array	true		A single column, a list or columns, or an array of columns to return from the inserted query.

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"
    } );

SQL Server

INSERT INTO [users] ([email], [name])
OUTPUT INSERTED.[id]
VALUES (?, ?)

Postgres

INSERT INTO "users" ("email", "name")
VALUES (?, ?)
RETURNING "id"

SQLite

INSERT INTO "users" ("email", "name")
VALUES (?, ?)
RETURNING "id"

The returning function also applies to update and delete calls.

QueryBuilder

query.table( "users" )
    .returning( [ "id", "modifiedDate" ] )
    .where( "id", 1 )
    .update( { "email": "[e-mail]" } );

SQL Server

UPDATE [users]
SET [email] = ?
OUTPUT INSERTED.[id], INSERTED.[modifiedDate]
WHERE [id] = ?

Postgres

UPDATE "users"
SET "email" = ?
WHERE "id" = ?
RETURNING "id", "modifiedDate"

SQLite

UPDATE "users"
SET "email" = ?
WHERE "id" = ?
RETURNING "id", "modifiedDate"

QueryBuilder

query.table( "users" )
    .returning( "id" )
    .where( "active", 0 )
    .delete();

SQL Server

DELETE FROM [users]
OUTPUT DELETED.[id]
WHERE [active] = ?

Postgres

DELETE FROM "users" WHERE "active" = ?
RETURNING "id"

SQLite

DELETE FROM "users" WHERE "active" = ?
RETURNING "id"

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": "[e-mail]" } );

SQL Server

UPDATE [users]
SET [email] = ?
OUTPUT
    DELETED.modifiedDate AS oldModifiedDate,
    INSERTED.modifiedDate AS newModifiedDate
WHERE [id] = ?