Search…
Inserts, Updates, and Deletes
The following methods all have the same return value:
1
{
2
"result": "Value of the `result` parameter to `queryExecute`",
3
"query": "Return value of running `queryExecute` - a CFML query object"
4
}
Copied!
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
1
query.from( "users" )
2
.insert( {
3
"name" = "Robert",
4
"email" = "[email protected]",
5
"age" = 55
6
} );
Copied!
MySQL
1
INSERT INTO `users` (`age`, `email`, `name`)
2
VALUES (?, ?, ?)
Copied!
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
1
query.from( "users" )
2
.insert( {
3
"name" = "Robert",
4
"email" = "[email protected]",
5
"age" = { value = 55, cfsqltype = "CF_SQL_INTEGER" }
6
} );
Copied!
MySQL
1
INSERT INTO `users` (`age`, `email`, `name`)
2
VALUES (?, ?, ?)
Copied!
Raw values can be supplied to an insert statement.
QueryBuilder
1
query.from( "users" )
2
.insert( {
3
"name" = "Robert",
4
"email" = "[email protected]",
5
"updatedDate" = query.raw( "NOW()" )
6
} );
Copied!
MySQL
1
INSERT INTO `users` (`age`, `email`, `updatedDate`)
2
VALUES (?, ?, NOW())
Copied!
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
1
query.from( "users" ).insert( [
2
{ "email" = "[email protected]", "name" = "John Doe" },
3
{ "email" = "[email protected]", "name" = "Jane Doe" }
4
] );
Copied!
MySQL
Oracle
1
INSERT INTO `users` (`email`, `name`)
2
VALUES (?, ?), (?, ?)
Copied!
1
INSERT ALL
2
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
3
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
4
SELECT 1 FROM dual
Copied!

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 and SqlServerGrammar. 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
1
query.from( "users" )
2
.returning( "id" )
3
.insert( {
4
"email" = "foo",
5
"name" = "bar"
6
} );
Copied!
SQL Server
Postgres
1
INSERT INTO [users] ([email], [name])
2
OUTPUT INSERTED.[id]
3
VALUES (?, ?)
Copied!
1
INSERT INTO "users" ("email", "name")
2
VALUES (?, ?)
3
RETURNING "id"
Copied!

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
1
query.from( "users" )
2
.update( {
3
"email" = "foo",
4
"name" = "bar"
5
} );
Copied!
MySQL
1
UPDATE `users`
2
SET `email` = ?,
3
`name` = ?
Copied!
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
1
query.from( "users" )
2
.update( {
3
"email" = "foo",
4
"name" = "bar",
5
"updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
6
} );
Copied!
MySQL
1
UPDATE `users`
2
SET `email` = ?,
3
`name` = ?,
4
`updatedDate` = ?
Copied!
Any constraining of the update query should be done using the appropriate WHERE statement before calling update.
QueryBuilder
1
query.from( "users" )
2
.whereId( 1 )
3
.update( {
4
"email" = "foo",
5
"name" = "bar"
6
} );
Copied!
MySQL
1
UPDATE `users`
2
SET `email` = ?,
3
`name` = ?
4
WHERE `Id` = ?
Copied!
You can update a column based on another column using a raw expression.
QueryBuilder
1
query.from( "hits" )
2
.where( "page", "someUrl" )
3
.update( {
4
"count" = query.raw( "count + 1" )
5
} );
Copied!
MySQL
1
UPDATE `hits`
2
SET `count` = count + 1
3
WHERE `page` = ?
Copied!

Updating Null values

Null values can be inserted by using queryparam syntax:
1
query.from("user")
2
.whereId( 10 )
3
.update( {
4
manager_FK = { value = "", null=true },
5
} )
Copied!
if you are using full null support the following (easier) syntax is also allowed:
1
query.from("user")
2
.whereId( 10 )
3
.update( {
4
manager_FK = { value = null },
5
} )
Copied!

Updating with Subselects

Subselects can be used to update values by passing a closure as the value
1
qb.table( "employees" )
2
.update( {
3
"departmentName" = function( q ) {
4
q.from( "departments" )
5
.select( "name" )
6
.whereColumn( "employees.departmentId", "departments.id" );
7
} )
8
} );
Copied!
MySQL
1
UPDATE `employees`
2
SET `departmentName` = (
3
SELECT `name`
4
FROM `departments`
5
WHERE `employees`.`departmentId` = `departments`.`id`
6
)
Copied!
You can also pass a builder instance in place of the closure.
1
qb.table( "employees" )
2
.update( {
3
"departmentName" = qb.newQuery()
4
.from( "departments" )
5
.select( "name" )
6
.whereColumn( "employees.departmentId", "departments.id" )
7
} )
8
} );
Copied!

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.
1
qb.table( "employees" )
2
.join( "departments", "departments.id", "employees.departmentId" )
3
.update( {
4
"employees.departmentName": qb.raw( "departments.name" )
5
} );
Copied!
MySQL
SQL Server
Postgres
1
UPDATE `employees`
2
INNER JOIN `departments`
3
ON `departments`.`id` = `employees`.`departmentId`
4
SET `employees`.`departmentName` = departments.name
Copied!
1
UPDATE [employees]
2
SET [employees].[departmentName] = departments.name
3
FROM [employees]
4
INNER JOIN [departments]
5
ON [departments].[id] = [employees].[departmentId]
Copied!
1
UPDATE "employees"
2
SET "employees"."departmentName" = departments.name
3
FROM "departments"
4
WHERE "departments"."id" = "employees"."departmentId"
Copied!

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
1
query.from( "users" )
2
.whereId( 1 )
3
.addUpdate( {
4
"email" = "foo",
5
"name" = "bar"
6
} )
7
.when( true, function( q ) {
8
q.addUpdate( {
9
"foo": "yes"
10
} );
11
} )
12
.when( false, function( q ) {
13
q.addUpdate( {
14
"bar": "no"
15
} );
16
} )
17
.update();
Copied!
MySQL
1
UPDATE `users`
2
SET `email` = ?,
3
`foo` = ?,
4
`name` = ?
5
WHERE `Id` = ?
Copied!

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
1
query.from( "users" )
2
.where( "email", "foo" )
3
.updateOrInsert( {
4
"email" = "foo",
5
"name" = "baz"
6
} );
Copied!
MySQL
1
UPDATE `users`
2
SET `email` = ?,
3
`name` = ?
4
WHERE `email` = ?
5
LIMIT 1
Copied!
If the configured query returns 0 records, then an insert statement is performed.
QueryBuilder
1
query.from( "users" )
2
.where( "email", "foo" )
3
.updateOrInsert( {
4
"email" = "foo",
5
"name" = "baz"
6
} );
Copied!
MySQL
1
INSERT INTO `users` (`email`, `name`)
2
VALUES (?, ?)
Copied!

upsert

Name
Type
Required
Default
Description
values
struct | array<struct>
true
A struct or array of structs to insert into or update on the table.
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.
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.
1
qb.table( "users" )
2
.upsert(
3
values = [
4
{
5
"username": "johndoe",
6
"active": 1,
7
"createdDate": "2021-09-08 12:00:00",
8
"modifiedDate": "2021-09-08 12:00:00"
9
},
10
{
11
"username": "janedoe",
12
"active": 1,
13
"createdDate": "2021-09-10 10:42:13",
14
"modifiedDate": "2021-09-10 10:42:13"
15
},
16
],
17
target = [ "username" ],
18
update = [ "active", "modifiedDate" ],
19
);
Copied!
MySQL
SQL Server
Postgres
Oracle
1
INSERT INTO `users`
2
(`active`, `createdDate`, `modifiedDate`, `username`)
3
VALUES
4
(?, ?, ?, ?),
5
(?, ?, ?, ?)
6
ON DUPLICATE KEY UPDATE
7
`active` = VALUES(`active`),
8
`modifiedDate` = VALUES(`modifiedDate`)
Copied!
1
MERGE [users] AS [qb_target]
2
USING (VALUES (?, ?, ?, ?), (?, ?, ?, ?)) AS [qb_src]
3
([active], [createdDate], [modifiedDate], [username])
4
ON [qb_target].[username] = [qb_src].[username]
5
WHEN MATCHED THEN UPDATE
6
SET [active] = [qb_src].[active],
7
[modifiedDate] = [qb_src].[modifiedDate]
8
WHEN NOT MATCHED BY TARGET THEN INSERT
9
([active], [createdDate], [modifiedDate], [username])
10
VALUES
11
([active], [createdDate], [modifiedDate], [username])
Copied!
1
INSERT INTO "users"
2
("active", "createdDate", "modifiedDate", "username")
3
VALUES
4
(?, ?, ?, ?),
5
(? ,? ,? ,?)
6
ON CONFLICT ("username") DO UPDATE
7
"active" = EXCLUDED."active",
8
"modifiedDate" = EXCLUDED."modifiedDate"
Copied!
1
MERGE INTO "USERS" "QB_TARGET"
2
USING (
3
SELECT ?, ?, ?, ? FROM dual
4
UNION ALL
5
SELECT ?, ?, ?, ? FROM dual
6
) "QB_SRC"
7
ON "QB_TARGET"."USERNAME" = "QB_SRC"."USERNAME"
8
WHEN MATCHED THEN UPDATE
9
SET "ACTIVE" = "QB_SRC"."ACTIVE",
10
"MODIFIEDDATE" = "QB_SRC"."MODIFIEDDATE"
11
WHEN NOT MATCHED THEN INSERT
12
("ACTIVE", "CREATEDDATE", "MODIFIEDDATE", "USERNAME")
13
VALUES
14
("QB_SRC"."ACTIVE", "QB_SRC"."CREATEDDATE", "QB_SRC"."MODIFIEDDATE", "QB_SRC"."USERNAME")
Copied!
The update clause in a upsert can also accept raw values, making it very useful for tracking data like statistics.
1
qb.table( "stats" )
2
.upsert(
3
values = [
4
{ "postId": 1, "viewedDate": "2021-09-08", "views": 1 },
5
{ "postId": 2, "viewedDate": "2021-09-08", "views": 1 }
6
],
7
target = [ "postId", "viewedDate" ],
8
update = { "views": qb.raw( "stats.views + 1" ) }
9
);
Copied!
MySQL
SQL Server
Postgres
Oracle
1
INSERT INTO `stats`
2
(`postId`, `viewedDate`, `views`)
3
VALUES
4
(?, ?, ?),
5
(?, ?, ?)
6
ON DUPLICATE KEY UPDATE
7
`views` = stats.views + 1
Copied!
1
MERGE [stats] AS [qb_target]
2
USING (VALUES (?, ?, ?), (?, ?, ?)) AS [qb_src]
3
([postId], [viewedDate], [views])
4
ON [qb_target].[postId] = [qb_src].[postId]
5
AND [qb_target].[viewedDate] = [qb_src].[viewedDate]
6
WHEN MATCHED THEN UPDATE
7
SET [views] = stats.views + 1
8
WHEN NOT MATCHED BY TARGET THEN INSERT
9
([postId], [viewedDate], [views])
10
VALUES
11
([postId], [viewedDate], [views])
Copied!
1
INSERT INTO "stats"
2
("postId", "viewedDate", "views")
3
VALUES
4
(?, ?, ?),
5
(?, ?, ?)
6
ON CONFLICT ("postId", "viewedDate") DO UPDATE
7
"views" = stats.views + 1
Copied!
1
MERGE INTO "STATS" "QB_TARGET"
2
USING (
3
SELECT ?, ?, ? FROM dual
4
UNION ALL
5
SELECT ?, ?, ? FROM dual
6
) "QB_SRC"
7
ON "QB_TARGET"."POSTID" = "QB_SRC"."POSTID"
8
AND "QB_TARGET"."VIEWEDDATE" = "QB_SRC"."VIEWEDDATE"
9
WHEN MATCHED THEN UPDATE
10
SET "VIEWS" = stats.views + 1
11
WHEN NOT MATCHED THEN INSERT
12
("POSTID", "VIEWEDDATE", "VIEWS")
13
VALUES
14
("QB_SRC"."POSTID", "QB_SRC"."VIEWEDDATE", "QB_SRC"."VIEWS")
Copied!

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
1
query.from( "users" )
2
.where( "email", "foo" )
3
.delete();
Copied!
MySQL
1
DELETE FROM `users`
2
WHERE `email` = ?
Copied!
The id argument is a convenience to delete a single record by id.
QueryBuilder
1
query.from( "users" )
2
.delete( 1 );
Copied!
MySQL
1
DELETE FROM `users`
2
WHERE `id` = ?
Copied!
Last modified 1mo ago