How to Delete from Multiple Tables in the Same SQL Statement

Delete from two tables in one query

Can't you just separate them by a semicolon?

Delete from messages where messageid = '1';
Delete from usersmessages where messageid = '1'

Just use INNER JOIN as below

DELETE messages , usersmessages  FROM messages  INNER JOIN usersmessages  
WHERE messages.messageid= usersmessages.messageid and messages.messageid = '1'

Is it possible to delete from multiple tables in the same SQL statement?

Nope, you'd need to run multiple statements.

Because you need to delete from two tables, consider creating a temp table of the matching ids:

SELECT U.Id INTO #RecordsToDelete
FROM Users U
   JOIN LinkingTable J ON U.Id = J.U_Id
...

And then delete from each of the tables:

DELETE FROM Users 
WHERE Id IN (SELECT Id FROM #RecordsToDelete)

DELETE FROM LinkingTable
WHERE Id IN (SELECT Id FROM #RecordsToDelete)

Delete from multiple tables in one statement

Yes, changing the join on table2 to left join would do what you want. Rows in table1 that belong to the list and to table3 will all get deleted, regardless whether they also exist in table2. In the meantime, possible matching rows in table2 will be deleted too.

delete t1, t2
from table1 t1
left join table2 t2 on t1.id = t2.table1_id
inner join table3 t3 on t1.id = t3.table1_id
where t1.id in (?, ?, ?, ?);

I would suggest rewriting the join on table3 to an exists condition instead. This makes the intent of the query more obvious, and might perform better, especially if you have an index on table3(table1_id):

delete t1, t2
from table1 t1
left join table2 t2 on t1.id = t2.table1_id
where 
    t1.id in (?, ?, ?, ?)
    and exists (select 1 from table3 t3 where t3.table1_id = t1.id)

Delete from multiple tables with SqlCommand

Your Problem in the SQL Statement, it is not valid.

You should divide the statement into two ones:

First Delete Comments, then delete Posts

Sequence is Important

var deleteComments = new SqlCommand("DELETE Comments FROM Posts INNER JOIN Comments ON Posts.PostId = Comments.PostId WHERE Posts.PostId = @PostId;");
        deleteComments.Parameters.AddWithValue("@PostId", postId);
        _dataAccess.ExecuteQuery(deleteComments);
        
var deletePosts = new SqlCommand("DELETE Posts WHERE PostId= @PostId;");
        deletePosts.Parameters.AddWithValue("@PostId", postId);
        _dataAccess.ExecuteQuery(deletePosts);

The other option, using one statement:

var delete = new SqlCommand("DELETE Comments FROM Posts INNER JOIN Comments ON Posts.PostId = Comments.PostId WHERE Posts.PostId = @PostId; DELETE Posts WHERE PostId= @PostId;");
            delete.Parameters.AddWithValue("@PostId", postId);
            _dataAccess.ExecuteQuery(delete);

More Explanation:

Using the Following Prepared SQL Script using SQL Studio (SSMS):

CREATE TABLE Posts (PostId  INT, PostText varchar(20))
CREATE TABLE Comments (CommentId INT, PostId INT, CommentText varchar(20))

INSERT INTO Posts VALUES (1, 'text')
INSERT INTO Comments VALUES (1,1, 'comment here')

when I run your DELETE statement

DELETE Posts, Comments FROM Posts INNER JOIN Comments ON Posts.PostId = Comments.PostId WHERE Posts.PostId = 1

It gives me the same error

When I run

DELETE Comments FROM Posts INNER JOIN Comments ON Posts.PostId = Comments.PostId WHERE Posts.PostId = 1;

DELETE Posts WHERE PostId = 1;

It works fine.

So the rule of thumb in such cases is to use SSMS (MS SQL Studio) to test your SQL statement first and then implement it in C#.

Mysql - delete from multiple tables with one query

You can define foreign key constraints on the tables with ON DELETE CASCADE option.

Then deleting the record from parent table removes the records from child tables.

Check this link : http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

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

(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

Delete rows from multiple tables in a single statement

The standard way to do that is to declare the foreign key as ON DELETE CASCADE. Doing so, if a user is deleted, all articles related to it will also be deleted.

CREATE TABLE articles (
  user_id int REFERENCES users (id) ON DELETE CASCADE);

Demo: db<>fiddle

If for some reason you're not able to change the table structure, consider using a DELETE ... RETURNING from users inside a CTE and then use the returned values in a outer DELETE on articles:

WITH j (id) AS (
  DELETE FROM users WHERE id = 1
  RETURNING id
)
DELETE FROM articles a USING j 
WHERE a.user_id = j.id;