Inserts, Updates, and Deletes

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" = "robert@test.com",
"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" = "robert@test.com",
"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" = "robert@test.com",
"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" = "john@example.com", "name" = "John Doe" },
{ "email" = "jane@example.com", "name" = "Jane Doe" }
] );
MySQL
Oracle
MySQL
INSERT INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)
Oracle
INSERT ALL
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
SELECT 1 FROM dual

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
query.from( "users" )
.returning( "id" )
.insert( {
"email" = "foo",
"name" = "bar"
} );
SQL Server
Postgres
SQL Server
INSERT INTO [users] ([email], [name])
OUTPUT INSERTED.[id]
VALUES (?, ?)
Postgres
INSERT INTO "users" ("email", "name")
VALUES (?, ?)
RETURNING "id"

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 Lucee with full null support the following (easier) syntax is also allowed:

query.from("user")
.whereId( 10 )
.update( {
manager_FK = { value = null },
} )

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 (?, ?)

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` = ?