LogoLogo
8.7.0
8.7.0
  • Introduction
  • What's New?
  • Installation & Usage
  • Migration Guide
  • Contributing & Filing Issues
  • Query Builder
    • Getting a New Query
    • Building Queries
      • Selects
      • From
      • Joins
      • Wheres
      • Order By
      • Group By and Having
      • Limit, Offset, and Pagination
      • Locks
      • Unions
      • Common Table Expressions (i.e. CTEs)
      • Raw Expressions
      • When / Conditionals
      • Query Parameters and Bindings
    • Executing Queries
      • Retrieving Results
      • Aggregates
      • Inserts, Updates, and Deletes
    • Options and Utilities
      • Query Options
      • Clone and Reset
      • Return Format
      • Column Formatter
      • Parent Query
      • Interception Points
    • Debugging
  • Schema Builder
    • Overview
    • Create
    • Columns
    • Column Modifiers
    • Column Constraints
    • Creating Table Constraints
    • Alter
    • Drop
  • External Links
    • API Docs
    • Source Code
    • Issue Tracker
Powered by GitBook
On this page
  • insert
  • returning
  • update
  • Updating Null values
  • Updating with Subselects
  • Updating with Joins
  • addUpdate
  • updateOrInsert
  • upsert
  • delete

Was this helpful?

Edit on GitHub
Export as PDF
  1. Query Builder
  2. Executing Queries

Inserts, Updates, and Deletes

PreviousAggregatesNextOptions and Utilities

Last updated 3 years ago

Was this helpful?

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

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 or .

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

{}

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` = ?
QueryBuilder
query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar",
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
    } );
MySQL
UPDATE `users`
SET `email` = ?,
    `name` = ?,
    `updatedDate` = ?
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 full null support the following (easier) syntax is also allowed:

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

qb.table( "employees" )
    .update( {
		    "departmentName" = function( q ) {
		        q.from( "departments" )
		            .select( "name" )
		            .whereColumn( "employees.departmentId", "departments.id" );
		    } )
		} );
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" )
		    } )
		} );

Updating with Joins

qb will correctly format JOIN clauses in your UPDATE statements for your database grammar.

qb.table( "employees" )
    .join( "departments", "departments.id", "employees.departmentId" )
    .update( {
        "employees.departmentName": qb.raw( "departments.name" )
    } );
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"

addUpdate

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

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

upsert

Name

Type

Required

Default

Description

values

struct | array<struct>

true

A struct or array of structs to insert into or update on the table.

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.

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" ],
    );
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" ) }
    );
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")
VALUES
    (?, ?, ?),
    (?, ?, ?)
ON CONFLICT ("postId", "viewedDate") DO UPDATE
    "views" = stats.views + 1
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

Name

Type

Required

Default

Description

id

any

false

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

You can specify any options such as the SQL type by passing a struct with the parameters you would pass to .

A struct of column and value pairs to update. These column and value pairs are appended to any already set with the method.

This call must come after setting the query's table using or .

You can specify any options such as the SQL type by passing a struct with the parameters you would pass to .

Any constraining of the update query should be done using the appropriate statement before calling update.

OracleGrammar does not support JOIN clauses inUPDATE statements. Consider using in your UPDATE statement instead.

Adds values to a later , similar to .

A convenience argument for `where( "id", "=", arguments.id ). The query can be constrained by normal methods as well.

WHERE
subselects
WHERE
addUpdate
cfqueryparam
cfqueryparam
update
from
table
from
table
query param
query param
addSelect