How to delete from multiple tables in one DELETE statement in a .cfc file?

Question

I have two tables. A device table and a location table. There can only be one device in the device table but there can be multiple devices with multiple locations in the location table.

If a user deletes a device, I need to also delete the locations with the same id from the location table.

Is there a way to delete from multiple tables in a Delete statement (using sql server) from within a cfc page?

I have tried to write a <cfif> when I do my initial delete, to check if there were records deleted and if so, run the next delete statement that deletes the devices from the table.

I have also tried to use this format.

delete T1, T2
from T1 
inner join T2 on T1.device_id = T2.device_id
where T2.device_id = '111';

T1 = device table, T2 = Location table

<cfif ARGUMENTS.submitButton eq 'btn_Delete'>
   <cfquery name="DeleteDevice" datasource="#session.dsn#" maxRows=1 >
        DELETE from #session.tq#device
        WHERE device_id = <cfqueryparam value="#formStruct.deviceId#" cfsqltype="CF_SQL_VARCHAR">
    </cfquery>
 </cfif>

<cfif DeleteDevice.RecordCount eq 1>
<cfquery name="DeleteLocation" datasource="#session.dsn#" maxRows=1 >
        DELETE from #session.tq#device_location_xref
        WHERE device_location_xref_recno=<cfqueryparam value="#formStruct.deviceId#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>

If you’re thinking that it worked. It didn’t. Hence me asking the question. Hmmm...but you think this is the right path? Maybe I need to wrap both deletes in the first if statement. Trying that because at this point I can’t recall way I did it. — Ash, May 31 '19 at 04:30
You could add a cascading delete in the database for the child table. Also, be careful with `#session.tq#`. If you don't use or do some other validation, your queries are vulnerable to exploitation. — Redtopia, May 31 '19 at 07:06
Are you saying that the code that did not work is the `` condition? If so, try adding the `result=variablename` to your initial `` statement. Then use that variable name to check the `RecordCount`. — Miguel-F, May 31 '19 at 11:39
If there is a foreign key constraint on `device_location_xref`to `device`, then you'll have to delete from the 2nd table first. Regardless, always do the second delete, then the first. If there are no linked records, then nothing will get deleted and you can move on. Also, are your tables named after `session.tq...something`? That seems like it could become a data nightmare. — Shawn, May 31 '19 at 12:43
And I've always found it much better to use a `void` flag for a record rather than to actually delete it. It's _MUCH_ easier to set your `void` to false than it is to restore from backup if someone accidentally deletes the wrong records. — Shawn, May 31 '19 at 12:45
(Edit) *Is there a way to delete from multiple tables in a Delete statement* No. SQL Server only allows deletion from a single table at a time. The reason your `cfif` code doesn't work is because DELETE statements don't return a "query" object - in CF parlance. To determine the number of records affected, you need to use the cfquery `"result"` attribute, as Miguel-F described. Having said all that, **why does it matter**? As Shawn described, you can just delete any records from the *secondary* table first, then the primary table and you're done. (cont'd) — SOS, May 31 '19 at 13:37
Though if you do choose to use "hard deletes", you should be wrap the whole thing in a transaction, to ensure the statements are handled as a single block - i.e. everything succeeds, or everything fails. Note - That still applies, even if you put both statements within a single cfquery tag. (The "soft-delete" approach Shawn described would work as well, as an alternative to actually removing the records) — SOS, May 31 '19 at 13:38
As an aside, you're misunderstanding `maxRows`. It only applies to statements that return a result set, i.e. SELECT. It has no impact whatsoever on DELETE/INSERT/UPDATE statements. — SOS, May 31 '19 at 17:29

SOS · Accepted Answer · 2019-06-01T19:12:30.113

(I thought it would be helpful to consolidate all of the useful comments under a Community wiki answer)

Can you DELETE from multiple tables in a single statement?

Not with SQL Server, no. Some databases, like MySQL, support multi-table delete statements. SQL Server does not. You'll need multiple delete statements. However, if the goal is simply to delete the records, it makes little difference.

What's wrong with the current code?

There are several reasons the current code isn't working. First, the initial delete query only executes IF the form was submitted. The second code block references that same query, but doesn't verify it actually exists first. That'll cause an undefined variable error if the form wasn't submitted yet.
Second, and more importantly, DELETE statements never return a "query" object, in CF parlance. So even if the first query did execute, the query variable DeleteDevice will never exist. Again, resulting in an undefined variable error when the code tries to use it here: <cfif DeleteDevice.RecordCount eq 1>.

Though unless there's more to it,... that cfif serves little purpose IMO. If records exist for a deviceID, they'll be deleted. If not, nothing will happen. Any negligible savings of skipping the second delete is outweighed by the added code complexity.
Lastly, though it doesn't cause a hard error, maxRows=1 isn't doing what you're thinking. It has no affect whatsoever on DELETE statements and won't prevent a DELETE statement from removing multiple records. The maxRows attribute only applies to statements that return a "query" object, and is used limit the number of records contained in that query.

Then how can I handle multiple delete statements?

Since SQL Server doesn't support multi-table DELETE statements, you'll need two statements - no matter what. There are several ways to structure the SQL, each with their own pros and cons. However, in all cases you should use a transaction to group the related statements and ensure everything completes or fails as a single unit

Multiple cfquery's

Just run your two queries, one after the other, inside a transaction. Though do swap the statement order, as Shawn suggested. Due to the possibility of FK constraints, it's just a good practice to delete from child/FK tables first and the primary/PK table last.

<cftransaction> <cfquery datasource="#session.dsn#"> DELETE FROM device_location_xref WHERE ... </cfquery> <cfquery datasource="#session.dsn#"> DELETE FROM device WHERE ..... </cfquery> </cftransaction>
Multiple statements within a single cfquery

As JamesAMohler's answer demonstrates, placing both SQL statements within a single cfquery tag is another option. However, keep in mind the limitations Alex mentioned. Multiple statements can be disabled at the db/driver level, for security reasons. So this approach may not work in all environments.

<cftransaction> <cfquery datasource="#session.dsn#"> DELETE FROM device_location_xref WHERE ... DELETE FROM device WHERE ..... </cfquery> </cftransaction>

SQL Stored procedure

Another option is what Shawn suggested: put the sql logic into a stored procedure. Generally speaking, stored procedures are better suited for complex sql operations, and unlike cfquery, they can return multiple result sets.

CREATE PROCEDURE DeleteDevice
  @deviceID VARCHAR(50)
AS
BEGIN
    -- minimal error handling for demo only
    BEGIN TRAN
        DELETE FROM device_location_xref
        WHERE device_location_xref_recno = @deviceID

        DELETE from device
        WHERE device_id = @deviceID

    END TRAN

END

Then invoke it with cfstoredproc instead of cfquery.

<cfstoredproc procedure="DeleteDevice" datasource="#ds#"> 
   <cfprocparam type="in" value="#formStruct.deviceId#" 
          cfsqltype="cf_sql_varchar"> 
</cfstoredproc>

Soft vs Hard Deletes

Lastly, an alternative Shawn mentioned is a "soft-delete". Instead of physically removing the records, you could add a BIT column to the table(s). Then UPDATE that column flag whenever you need to mark a record as deleted. See this thread for a discussion of the pros and cons of soft-deletes.

      UPDATE TableName
      SET    IsDeleted   = 1
      WHERE  TheIDColumn = 12345

Additional vote for using a stored procedure. Much easier to update as needed, especially if there are no related application code changes. — Adrian J. Moreno, Jun 03 '19 at 21:41

score 0 · Answer 2 · answered May 31 '19 at 05:49

0

Can you do something like this

<cfquery>
DECLARE @device_id varchar(20) = <cfqueryparam value="#formStruct.deviceId#" cfsqltype="CF_SQL_VARCHAR">

DELETE
FROM T1
WHERE device_id = @device_id 

DELETE
FROM T2
WHERE device_id = @device_id 
</cfquery>

answered May 31 '19 at 05:49

James A Mohler

11,060
15
46
72

1

@Ash - Keep in mind that it's still executing *multiple* sql statements. So the usual rules still apply. Multiple related operations should be wrapped in a [transaction](https://en.wikipedia.org/wiki/Database_transaction) to ensure everything completes or fails as a single unit. – SOS May 31 '19 at 15:12
1

A cascading delete would be similar, but I'm extremely leery of using those for any type of delete. I'd rather maintain control and do it myself :-) – SOS May 31 '19 at 16:56
1

@Ash - It still begs the question what's the reason for wanting a single query? There are multiple ways to approach it, but the right one(s) depends on what's the reasoning here (beyond ensuring the records are deleted from both tables). – SOS May 31 '19 at 17:44
1

Note that a connection/datasource can be configured to allow only one statement per `cfquery`. I'm with @Ageax here and would recommend `cftransaction` and then execute multiple `cfquery` to delete related records. It's easier to read/maintain/extent and the overhead should be negligible. – Alex May 31 '19 at 19:15
Or better yet, send it to a stored procedure and let the database handle deleting database records. Granted, that will put code in two different places, but I firmly believe that a database is more than just a place to store stuff. Let it do the job it was made to do. – Shawn May 31 '19 at 21:49

score 0 · Answer 3 · answered Jun 02 '19 at 15:46

I would delete from both tables in one cfquery statement seeing as the second deletion is not dependent on the first deletion.

  <cfif comparenocase(ARGUMENTS.submitButton, 'btn_Delete') EQ 0> <!--- bit quicker to use compare --->
    <cfquery name="DeleteDevice" datasource="#session.dsn#"> 
    <!--- delete from first table --->
    DELETE FROM #session.tq#device
    WHERE device_id = <cfqueryparam value="#formStruct.deviceId#" cfsqltype="CF_SQL_VARCHAR">;

<!--- delete from second table --->    
    DELETE FROM #session.tq#device_location_xref
    WHERE device_location_xref_recno=<cfqueryparam value="#formStruct.deviceId#" cfsqltype="CF_SQL_VARCHAR">
    </cfquery>
    </cfif>

How to delete from multiple tables in one DELETE statement in a .cfc file?

3 Answers3

Can you DELETE from multiple tables in a single statement?

What's wrong with the current code?

Then how can I handle multiple delete statements?

Soft vs Hard Deletes