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
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
returning
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
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
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
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
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" )
delete
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` = ?