The following methods all have the same return value:
Copy {
"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 A struct or array of structs to insert in to the table.
Any additional queryExecute
options.
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:
Copy query .from ( "users" )
.insert ( {
"name" = "Robert" ,
"email" = "robert@test.com" ,
"age" = 55
} );
Copy 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
.
Copy query .from ( "users" )
.insert ( {
"name" = "Robert" ,
"email" = "robert@test.com" ,
"age" = { value = 55, cfsqltype = "CF_SQL_INTEGER" }
} );
Copy INSERT INTO `users` ( `age` , `email` , `name` )
VALUES (?, ?, ?)
Raw values can be supplied to an insert statement.
Copy query .from ( "users" )
.insert ( {
"name" = "Robert" ,
"email" = "robert@test.com" ,
"updatedDate" = query.raw( "NOW()" )
} );
Copy 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
!
Copy query .from ( "users" ) .insert ( [
{ "email" = "john@example.com" , "name" = "John Doe" } ,
{ "email" = "jane@example.com" , "name" = "Jane Doe" }
] );
MySQL Oracle
Copy INSERT INTO `users` ( `email` , `name` )
VALUES (?, ?), (?, ?)
Copy INSERT ALL
INTO "USERS" ( "EMAIL" , "NAME" ) VALUES (?, ?)
INTO "USERS" ( "EMAIL" , "NAME" ) VALUES (?, ?)
SELECT 1 FROM dual
insertIgnore
Name Type Required Default Description A struct or array of structs to insert in to the table.
An array of key column names to match on. (SQL Server and Oracle grammars only.)
Any additional queryExecute
options.
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
Copy query .from ( "users" )
.insertIgnore (
values = [
{ "email" = "foo" , "name" = "bar" } ,
{ "email" = "baz" , "name" = "bam" }
] ,
target = [ "email" ]
);
MySQL SQL Server Postgres Oracle
Copy INSERT IGNORE INTO `users` ( `email` , `name` )
VALUES (?, ?), (?, ?)
Copy 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]);
Copy INSERT INTO "users" ( "email" , "name" )
VALUES (?, ?), (?, ?)
ON CONFLICT DO NOTHING
Copy 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 A callback or builder instance to serve as the source of the insert.
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.
Any additional queryExecute
options.
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.
Copy qb .from ( "users" )
.insertUsing ( function ( q ) {
q .from ( "activeDirectoryUsers" )
.select ( [ "email" , "modifiedDate AS createdDate" ] )
.where ( "active" , 1 );
} );
Copy 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.
Copy qb .from ( "users" )
.insertUsing (
columns = [ "email" , "createdDate" ] ,
source = function ( q ) {
q .from ( "activeDirectoryUsers" )
.select ( [ "email" , "modifiedDate" ] )
.where ( "active" , 1 );
}
);
Copy INSERT INTO `users` ( `email` , `createdDate` )
SELECT `email` , `modifiedDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?
Alternatively, the source can be defined as a QueryBuilder object:
Copy qb.from( "users" )
.insertUsing(
qb.newQuery()
.from( "activeDirectoryUsers" )
.select( [ "email", "modifiedDate AS createdDate" ] )
.where( "active", 1 )
);
Copy INSERT INTO `users` ( `email` , `createdDate` )
SELECT `email` , `modifiedDate` AS `createdDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?
returning
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.
Copy query .from ( "users" )
.returning ( "id" )
.insert ( {
"email" = "foo" ,
"name" = "bar"
} );
SQL Server Postgres
Copy INSERT INTO [users] ([email], [name])
OUTPUT INSERTED.[id]
VALUES (?, ?)
Copy INSERT INTO "users" ( "email" , "name" )
VALUES (?, ?)
RETURNING "id"
update
A struct of column and value pairs to update. These column and value pairs are appended to any already set with the addUpdate
method.
Any additional queryExecute
options.
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.
Copy query .from ( "users" )
.update ( {
"email" = "foo" ,
"name" = "bar"
} );
Copy 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
.
Copy query .from ( "users" )
.update ( {
"email" = "foo" ,
"name" = "bar" ,
"updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
} );
Copy UPDATE `users`
SET `email` = ?,
`name` = ?,
`updatedDate` = ?
Any constraining of the update query should be done using the appropriate WHERE statement before calling update
.
Copy query .from ( "users" )
.whereId ( 1 )
.update ( {
"email" = "foo" ,
"name" = "bar"
} );
Copy UPDATE `users`
SET `email` = ?,
`name` = ?
WHERE `Id` = ?
You can update a column based on another column using a raw expression.
Copy query .from ( "hits" )
.where ( "page" , "someUrl" )
.update ( {
"count" = query.raw( "count + 1" )
} );
Copy UPDATE `hits`
SET `count` = count + 1
WHERE `page` = ?
Updating Null values
Null values can be inserted by using queryparam syntax:
Copy 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:
Copy 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
Copy qb.table( "employees" )
. update ( {
"departmentName" = function ( q ) {
q.from( "departments" )
. select ( "name" )
.whereColumn( "employees.departmentId" , "departments.id" );
} )
} );
MySQL
Copy 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.
Copy 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.
Copy qb.table( "employees" )
. join ( "departments" , "departments.id" , "employees.departmentId" )
. update ( {
"employees.departmentName" : qb.raw( "departments.name" )
} );
MySQL SQL Server Postgres
Copy UPDATE `employees`
INNER JOIN `departments`
ON `departments` . `id` = `employees` . `departmentId`
SET `employees` . `departmentName` = departments.name
Copy UPDATE [employees]
SET [employees].[departmentName] = departments.name
FROM [employees]
INNER JOIN [departments]
ON [departments].[id] = [employees].[departmentId]
Copy UPDATE "employees"
SET "employees" . "departmentName" = departments.name
FROM "departments"
WHERE "departments" . "id" = "employees" . "departmentId"
addUpdate
A struct of column and value pairs to add to the update clause.
Adds values to a later update
, similar to addSelect
.
Copy 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 ();
Copy UPDATE `users`
SET `email` = ?,
`foo` = ?,
`name` = ?
WHERE `Id` = ?
updateOrInsert
A struct of column and value pairs to either update or insert.
Any additional queryExecute
options.
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.
Copy query .from ( "users" )
.where ( "email" , "foo" )
.updateOrInsert ( {
"email" = "foo" ,
"name" = "baz"
} );
Copy UPDATE `users`
SET `email` = ?,
`name` = ?
WHERE `email` = ?
LIMIT 1
If the configured query returns 0 records, then an insert statement is performed.
Copy query .from ( "users" )
.where ( "email" , "foo" )
.updateOrInsert ( {
"email" = "foo" ,
"name" = "baz"
} );
Copy INSERT INTO `users` ( `email` , `name` )
VALUES (?, ?)
upsert
struct | array<struct> | array<string>
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.
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.
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
.
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.
Boolean flag to delete any unmatched source records as part the upsert. (SQL Server only.)
Any additional queryExecute
options.
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.
Copy 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
Copy INSERT INTO `users`
( `active` , `createdDate` , `modifiedDate` , `username` )
VALUES
(?, ?, ?, ?),
(?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
`active` = VALUES ( `active` ),
`modifiedDate` = VALUES ( `modifiedDate` )
Copy 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])
Copy INSERT INTO "users"
( "active" , "createdDate" , "modifiedDate" , "username" )
VALUES
(?, ?, ?, ?),
(? ,? ,? ,?)
ON CONFLICT ( "username" ) DO UPDATE
"active" = EXCLUDED. "active" ,
"modifiedDate" = EXCLUDED. "modifiedDate"
Copy 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.
Copy 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
Copy INSERT INTO `stats`
( `postId` , `viewedDate` , `views` )
VALUES
(?, ?, ?),
(?, ?, ?)
ON DUPLICATE KEY UPDATE
`views` = stats.views + 1
Copy 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])
Copy INSERT INTO "stats"
( "postId" , "viewedDate" , "views" )
VALUES
(?, ?, ?),
(?, ?, ?)
ON CONFLICT ( "postId" , "viewedDate" ) DO UPDATE
"views" = stats.views + 1
Copy 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
.
Copy 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 SQL Server Postgres Oracle
Copy INSERT INTO `users`
( `username` , `active` , `createdDate` , `modifiedDate` )
SELECT `username` , `active` , `createdDate` , `modifiedDate`
FROM `activeDirectoryUsers`
ON DUPLICATE KEY UPDATE
`active` = VALUES ( `active` ),
`modifiedDate` = VALUES ( `modifiedDate` )
Copy 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]);
Copy 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"
Copy 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
A convenience argument for `where( "id", "=", arguments.id ). The query can be constrained by normal WHERE methods as well.
The name of the id column for the delete shorthand.
Any additional queryExecute
options.
If true
, returns the raw SQL string instead of running the query. Useful for debugging.
Deletes all records that the query returns.
Copy query .from ( "users" )
.where ( "email" , "foo" )
.delete ();
Copy DELETE FROM `users`
WHERE `email` = ?
The id
argument is a convenience to delete a single record by id.
Copy query .from ( "users" )
.delete ( 1 );
Copy DELETE FROM `users`
WHERE `id` = ?