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
.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. |
You can insert a single record by passing a struct:
QueryBuilder
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" = "[email protected]",
"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" = "[email protected]",
"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" = "[email protected]", "name" = "John Doe" },
{ "email" = "[email protected]", "name" = "Jane Doe" }
] );
MySQL
Oracle
INSERT INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)
INSERT ALL
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
SELECT 1 FROM dual
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. |
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
SQL Server
Postgres
Oracle
INSERT IGNORE INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)
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]);
INSERT INTO "users" ("email", "name")
VALUES (?, ?), (?, ?)
ON CONFLICT DO NOTHING
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")
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. |
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` = ?
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. |
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` = ?
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 },
} )
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" )
} )
} );
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
SQL Server
Postgres
UPDATE `employees`
INNER JOIN `departments`
ON `departments`.`id` = `employees`.`departmentId`
SET `employees`.`departmentName` = departments.name
UPDATE [employees]
SET [employees].[departmentName] = departments.name
FROM [employees]
INNER JOIN [departments]
ON [departments].[id] = [employees].[departmentId]
UPDATE "employees"
SET "employees"."departmentName" = departments.name
FROM "departments"
WHERE "departments"."id" = "employees"."departmentId"
Name | Type | Required | Default | Description |
values | struct | true | ​ | A struct of column and value pairs to add to the update clause. |
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` = ?
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 (?, ?)
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
SQL Server
Postgres
Oracle
INSERT INTO `users`
(`active`, `createdDate`, `modifiedDate`, `username`)
VALUES
(?, ?, ?, ?),
(?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
`active` = VALUES(`active`),
`modifiedDate` = VALUES(`modifiedDate`)
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])
INSERT INTO "users"
("active", "createdDate", "modifiedDate", "username")
VALUES
(?, ?, ?, ?),
(? ,? ,? ,?)
ON CONFLICT ("username") DO UPDATE
"active" = EXCLUDED."active",
"modifiedDate" = EXCLUDED."modifiedDate"
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
SQL Server
Postgres
Oracle
INSERT INTO `stats`
(`postId`, `viewedDate`, `views`)
VALUES
(?, ?, ?),
(?, ?, ?)
ON DUPLICATE KEY UPDATE
`views` = stats.views + 1
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])
INSERT INTO "stats"
("postId", "viewedDate", "views")