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.

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

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" }
] );

INSERT INTO `users` (`email`, `name`)
VALUES (?, ?), (?, ?)

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"
    } );

INSERT INTO [users] ([email], [name])
OUTPUT INSERTED.[id]
VALUES (?, ?)

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

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

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

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

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

PreviousAggregates NextOptions and Utilities

Last updated 3 years ago