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'
OR
Just use INNER JOIN
as below
DELETE messages , usersmessages FROM messages INNER JOIN usersmessages
WHERE messages.messageid= usersmessages.messageid and messages.messageid = '1'
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
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)
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)
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 variableDeleteDevice
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 onDELETE
statements and won't prevent a DELETE statement from removing multiple records. ThemaxRows
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
ENDThen invoke it with
cfstoredproc
instead ofcfquery
.<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 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#.
Related Topics
Xcode 4 and Core Data: How to Enable SQL Debugging
How to Find the MySQL My.Cnf Location
Insert Text With Single Quotes in Postgresql
Bash Script to Insert Values in MySQL
Commit Data in a MySQL Container
SQL Query Return Data from Multiple Tables
SQL Split Values to Multiple Rows
Ruby Gem MySQL2 Install Failing
Rails 4 Like Query - Activerecord Adds Quotes
Correct MySQL Configuration For Ruby on Rails Database.Yml File
Accessing an Sqlite Database in Swift
Get Top N Records For Each Group of Grouped Results
Refactor a Pl/Pgsql Function to Return the Output of Various Select Queries
How to Do an Update Statement With Join in SQL Server