Skip to content

Latest commit

 

History

History
1009 lines (870 loc) · 31.5 KB

inserts-updates-deletes.md

File metadata and controls

1009 lines (870 loc) · 31.5 KB

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

{% hint style="info" %} insert, update, and delete actions always return a query object for query, regardless of your configured returnFormat. {% endhint %}

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.

{% hint style="warning" %} This call must come after setting the query's table using from or table. {% endhint %}

You can insert a single record by passing a struct:

{% code title="QueryBuilder" %}

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "[email protected]",
        "age" = 55
    } );

{% endcode %}

{% code title="MySQL" %}

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

{% endcode %}

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

{% code title="QueryBuilder" %}

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "[email protected]",
        "age" = { value = 55, cfsqltype = "CF_SQL_INTEGER" }
    } );

{% endcode %}

{% code title="MySQL" %}

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

{% endcode %}

Raw values can be supplied to an insert statement.

{% code title="QueryBuilder" %}

query.from( "users" )
    .insert( {
        "name" = "Robert",
        "email" = "[email protected]",
        "updatedDate" = query.raw( "NOW()" )
    } );

{% endcode %}

{% code title="MySQL" %}

INSERT INTO `users` (`age`, `email`, `updatedDate`)
VALUES (?, ?, NOW())

{% endcode %}

Multiple rows can be inserted in a batch by passing an array of structs to insert.

{% hint style="info" %} 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! {% endhint %}

{% code title="QueryBuilder" %}

query.from( "users" ).insert( [
    { "email" = "john@example.com", "name" = "John Doe" },
    { "email" = "[email protected]", "name" = "Jane Doe" }
] );

{% endcode %}

{% tabs %} {% tab title="MySQL" %}

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

{% endtab %}

{% tab title="Oracle" %}

INSERT ALL
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
INTO "USERS" ("EMAIL", "NAME") VALUES (?, ?)
SELECT 1 FROM dual

{% endtab %} {% endtabs %}

insertIgnore

Name Type Required Default Description
values struct | array<struct> true A struct or array of structs to insert in to the table.
target array<string> false [] An array of key column names to match on. (SQL Server and Oracle grammars only.)
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.

{% hint style="warning" %} This call must come after setting the query's table using from or table. {% endhint %}

Inserts data into a table while ignoring duplicate key conflicts.

{% hint style="info" %} target is only required for SQLServerGrammar and OracleGrammar {% endhint %}

{% code title="QueryBuilder" %}

query.from( "users" )
    .insertIgnore(
        values = [
            { "email" = "foo", "name" = "bar" },
            { "email" = "baz", "name" = "bam" }
        ],
        target = [ "email" ]
    );

{% endcode %}

{% tabs %} {% tab title="MySQL" %}

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

{% endtab %}

{% tab title="SQL Server" %}

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

{% endtab %}

{% tab title="Postgres" %}

INSERT INTO "users" ("email", "name")
VALUES (?, ?), (?, ?)
ON CONFLICT DO NOTHING

{% endtab %}

{% tab title="Oracle" %}

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

{% endtab %} {% endtabs %}

insertUsing

Name Type Required Default Description
source function | QueryBuilder true A callback or builder instance to serve as the source of the insert.
columns array<string> false 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.
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.

{% hint style="warning" %} This call must come after setting the query's table using from or table. {% endhint %}

Inserts data into a table using a subquery as the source.

qb.from( "users" )
    .insertUsing( function( q ) {
        q.from( "activeDirectoryUsers" )
            .select( [ "email", "modifiedDate AS createdDate" ] )
            .where( "active", 1 );
    } );
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.

qb.from( "users" )
    .insertUsing(
        columns = [ "email", "createdDate" ],
        source = function( q ) {
            q.from( "activeDirectoryUsers" )
                 .select( [ "email", "modifiedDate" ] )
                 .where( "active", 1 );
        }
    );
INSERT INTO `users` (`email`, `createdDate`)
SELECT `email`, `modifiedDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?

Alternatively, the source can be defined as a QueryBuilder object:

qb.from( "users" )
    .insertUsing(
        qb.newQuery()
            .from( "activeDirectoryUsers" )
            .select( [ "email", "modifiedDate AS createdDate" ] )
            .where( "active", 1 )
    );

INSERT INTO `users` (`email`, `createdDate`)
SELECT `email`, `modifiedDate` AS `createdDate`
FROM `activeDirectoryUsers`
WHERE `active` = ?

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.

{% hint style="warning" %} This call must come after setting the query's table using from or table. {% endhint %}

Updates a table with a struct of column and value pairs.

{% code title="QueryBuilder" %}

query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );

{% endcode %}

{% code title="MySQL" %}

UPDATE `users`
SET `email` = ?,
    `name` = ?

{% endcode %}

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

{% code title="QueryBuilder" %}

query.from( "users" )
    .update( {
        "email" = "foo",
        "name" = "bar",
        "updatedDate" = { value = now(), cfsqltype = "CF_SQL_TIMESTAMP" }
    } );

{% endcode %}

{% code title="MySQL" %}

UPDATE `users`
SET `email` = ?,
    `name` = ?,
    `updatedDate` = ?

{% endcode %}

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

{% code title="QueryBuilder" %}

query.from( "users" )
    .whereId( 1 )
    .update( {
        "email" = "foo",
        "name" = "bar"
    } );

{% endcode %}

{% code title="MySQL" %}

UPDATE `users`
SET `email` = ?,
    `name` = ?
WHERE `Id` = ?

{% endcode %}

You can update a column based on another column using a raw expression.

{% code title="QueryBuilder" %}

query.from( "hits" )
    .where( "page", "someUrl" )
    .update( {
        "count" = query.raw( "count + 1" )
    } );

{% endcode %}

{% code title="MySQL" %}

UPDATE `hits`
SET `count` = count + 1
WHERE `page` = ?

{% endcode %}

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

{% tabs %} {% tab title="MySQL" %}

UPDATE `employees`
SET `departmentName` = (
    SELECT `name`
    FROM `departments`
    WHERE `employees`.`departmentId` = `departments`.`id`
)

{% endtab %} {% endtabs %}

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.

{% hint style="danger" %} OracleGrammar does not support JOIN clauses inUPDATE statements. Consider using subselects in your UPDATE statement instead. {% endhint %}

qb.table( "employees" )
    .join( "departments", "departments.id", "employees.departmentId" )
    .update( {
        "employees.departmentName": qb.raw( "departments.name" )
    } );

{% tabs %} {% tab title="MySQL" %}

UPDATE `employees`
INNER JOIN `departments`
    ON `departments`.`id` = `employees`.`departmentId`
SET `employees`.`departmentName` = departments.name

{% endtab %}

{% tab title="SQL Server" %}

UPDATE [employees]
SET [employees].[departmentName] = departments.name
FROM [employees]
INNER JOIN [departments]
    ON [departments].[id] = [employees].[departmentId]

{% endtab %}

{% tab title="Postgres" %}

UPDATE "employees"
SET "employees"."departmentName" = departments.name
FROM "departments"
WHERE "departments"."id" = "employees"."departmentId"

{% endtab %} {% endtabs %}

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.

{% code title="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();

{% endcode %}

{% code title="MySQL" %}

UPDATE `users`
SET `email` = ?,
    `foo` = ?,
    `name` = ?
WHERE `Id` = ?

{% endcode %}

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.

{% code title="QueryBuilder" %}

query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );

{% endcode %}

{% code title="MySQL" %}

UPDATE `users`
SET `email` = ?,
    `name` = ?
WHERE `email` = ?
LIMIT 1

{% endcode %}

If the configured query returns 0 records, then an insert statement is performed.

{% code title="QueryBuilder" %}

query.from( "users" )
    .where( "email", "foo" )
    .updateOrInsert( {
        "email" = "foo",
        "name" = "baz"
    } );

{% endcode %}

{% code title="MySQL" %}

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

{% endcode %}

upsert

Name Type Required Default Description
values struct | array<struct> | array<string> true 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.
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.
source function | QueryBuilder false null 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.
deleteUmatched boolean false false Boolean flag to delete any unmatched source records as part the upsert. (SQL Server only.)
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.

{% hint style="warning" %} In most database grammars, the target columns are required to be primary key or unique indexes. {% endhint %}

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

{% tabs %} {% tab title="MySQL" %}

INSERT INTO `users`
    (`active`, `createdDate`, `modifiedDate`, `username`)
VALUES
    (?, ?, ?, ?),
    (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

{% endtab %}

{% tab title="SQL Server" %}

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])

{% endtab %}

{% tab title="Postgres" %}

INSERT INTO "users"
    ("active", "createdDate", "modifiedDate", "username")
VALUES
    (?, ?, ?, ?),
    (? ,? ,? ,?)
ON CONFLICT ("username") DO UPDATE
    "active" = EXCLUDED."active",
    "modifiedDate" = EXCLUDED."modifiedDate"

{% endtab %}

{% tab title="Oracle" %}

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

{% endtab %} {% endtabs %}

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

{% tabs %} {% tab title="MySQL" %}

INSERT INTO `stats`
    (`postId`, `viewedDate`, `views`)
VALUES
    (?, ?, ?),
    (?, ?, ?)
ON DUPLICATE KEY UPDATE
    `views` = stats.views + 1

{% endtab %}

{% tab title="SQL Server" %}

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])

{% endtab %}

{% tab title="Postgres" %}

INSERT INTO "stats"
    ("postId", "viewedDate", "views")
VALUES
    (?, ?, ?),
    (?, ?, ?)
ON CONFLICT ("postId", "viewedDate") DO UPDATE
    "views" = stats.views + 1

{% endtab %}

{% tab title="Oracle" %}

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

{% endtab %} {% endtabs %}

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.

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

{% tabs %} {% tab title="MySQL" %}

INSERT INTO `users`
    (`username`, `active`, `createdDate`, `modifiedDate`)
SELECT `username`, `active`, `createdDate`, `modifiedDate`
FROM `activeDirectoryUsers`
ON DUPLICATE KEY UPDATE
    `active` = VALUES(`active`),
    `modifiedDate` = VALUES(`modifiedDate`)

{% endtab %}

{% tab title="SQL Server" %}

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

{% endtab %}

{% tab title="Postgres" %}

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"

{% endtab %}

{% tab title="Oracle" %}

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

{% endtab %} {% endtabs %}

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.

{% code title="QueryBuilder" %}

query.from( "users" )
    .where( "email", "foo" )
    .delete();

{% endcode %}

{% code title="MySQL" %}

DELETE FROM `users`
WHERE `email` = ?

{% endcode %}

The id argument is a convenience to delete a single record by id.

{% code title="QueryBuilder" %}

query.from( "users" )
    .delete( 1 );

{% endcode %}

{% code title="MySQL" %}

DELETE FROM `users`
WHERE `id` = ?

{% endcode %}

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.

{% hint style="danger" %} returning is only supported in PostgresGrammar, SqlServerGrammar, and SQLiteGrammar. Using this method on unsupported grammars will result in an UnsupportedOperation exception. Be aware that using this method constrains your grammar choices. {% endhint %}

Specifies columns to be returned from the insert query.

query.from( "users" )
    .returning( "id" )
    .insert( {
        "email" = "foo",
        "name" = "bar"
    } );

{% tabs %} {% tab title="SQL Server" %}

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

{% endtab %}

{% tab title="Postgres" %}

INSERT INTO "users" ("email", "name")
VALUES (?, ?)
RETURNING "id"

{% endtab %}

{% tab title="SQLite" %}

INSERT INTO "users" ("email", "name")
VALUES (?, ?)
RETURNING "id"

{% endtab %} {% endtabs %}

The returning function also applies to update and delete calls.

{% code title="QueryBuilder" %}

query.table( "users" )
    .returning( [ "id", "modifiedDate" ] )
    .where( "id", 1 )
    .update( { "email": "[email protected]" } );

{% endcode %}

{% tabs %} {% tab title="SQL Server" %}

UPDATE [users]
SET [email] = ?
OUTPUT INSERTED.[id], INSERTED.[modifiedDate]
WHERE [id] = ?

{% endtab %}

{% tab title="Postgres" %}

UPDATE "users"
SET "email" = ?
WHERE "id" = ?
RETURNING "id", "modifiedDate"

{% endtab %}

{% tab title="SQLite" %}

UPDATE "users"
SET "email" = ?
WHERE "id" = ?
RETURNING "id", "modifiedDate"

{% endtab %} {% endtabs %}

query.table( "users" )
    .returning( "id" )
    .where( "active", 0 )
    .delete();

{% tabs %} {% tab title="SQL Server" %}

DELETE FROM [users]
OUTPUT DELETED.[id]
WHERE [active] = ?

{% endtab %}

{% tab title="Postgres" %}

DELETE FROM "users" WHERE "active" = ?
RETURNING "id"

{% endtab %}

{% tab title="SQLite" %}

DELETE FROM "users" WHERE "active" = ?
RETURNING "id"

{% endtab %} {% endtabs %}

You can also use raw Expressions in a returning call. This is especially useful for SQL Server returning both the old and new values from an update call.

{% code title="QueryBuilder" %}

qb.from( "users" )
    .where( "id", 1 )
    .returningRaw( [
        "DELETED.modifiedDate AS oldModifiedDate",
        "INSERTED.modifiedDate AS newModifiedDate"
    ] )
    .update( { "email": "[email protected]" } );

{% endcode %}

{% tabs %} {% tab title="SQL Server" %}

UPDATE [users]
SET [email] = ?
OUTPUT
    DELETED.modifiedDate AS oldModifiedDate,
    INSERTED.modifiedDate AS newModifiedDate
WHERE [id] = ?

{% endtab %} {% endtabs %}