Skip to main content
N
Newsgroup_User
Inspiring
September 8, 2006
Question

JOIN DELETE - SQL

  • September 8, 2006
  • 6 replies
  • 766 views
Is it possible to use a JOIN to delete multiple rows from multiple tables
using a common key?

Lets say I have 3 tabels (Info, Guests, Host) and they all share a common
column (show_id). I want to remove all the records from each table that have
the same 'show_id'.

DELETE FROM Info, Guests, Hosts
WHERE show_id = #URL.show_id#

Will this work? I don't have a live database to work with at the moment and
I want to start the CFCs for it and came across this situation.

Thanks


This topic has been closed for replies.

6 replies

A
Anonymous
September 12, 2006

If your database supports storedprocedures or triggers, you should consider
these alternatives as well.


Good luck!
davidsimms
davidsimms
Inspiring
September 12, 2006
Wally,

That's not what is meant by cascading referential integrity. Instead, you should explicity set up relationships in the database so the database knows that when a record is deleted in one table, related rows in other tables also need to be deleted.

The query would be as simple as:

DELETE FROM radioShowInfo
WHERE show_id = #arguments.show#

but rows will also be deleted in any tables related to radioShowInfo by the database.

To learn more, use SQL Server's Books Online and lookup "referential integrity."

David
N
Newsgroup_UserAuthor
Inspiring
September 11, 2006
I am a wee bit confused on the <cftransaction> tag. I am doing this out
of a CFC and have all 3 deletes set up in a function. Is that any
different than setting up the 3 deletes in a <cftransaction> tag?

Yes setting them up in a function just groups the query in your code.
<cftransaction> groups the queries in the database. Assuming you are
using a database that understands the transaction concept. By grouping
queries in a transaction, all the queries must succeed or none of them
are committed. So, if something happens to prevent the third delete,
then none of the deletes happen and you don't have inconsistent data.

You can easily put a <cftransaction ...> in to a function.
P
paross1
Participating Frequently
September 11, 2006
He just needs to make sure that his queries are within three different sets of cfquery tags within the cftransaction.

Phil
davidsimms
davidsimms
Inspiring
September 11, 2006
Wally,

As a general rule, you should never use ColdFusion to do the database's job. Unless there's some mitigating circumstance, this sounds like a scenario where you should use cascading referential integrity so the database handles these deletes on its own.

David
N
Newsgroup_UserAuthor
Inspiring
September 10, 2006
I am a wee bit confused on the <cftransaction> tag. I am doing this out of a
CFC and have all 3 deletes set up in a function. Is that any different than
setting up the 3 deletes in a <cftransaction> tag?

"Ian Skinner" <ian.skinner@bloodsource.org> wrote in message
news:edsh8n$d0i$1@forums.macromedia.com...
>I doubt that will work, but I am not sure.
>
> If it does not you can use the <cftransaction> tag to group all the
> deletes together so that they all happen or none of them happen.
>
> <cftransaction>
> DELETE FROM Info
> WHERE show_id = #URL.show_id#
>
> DELETE FROM Guests
> WHERE show_id = #URL.show_id#
>
> DELETE FROM Hosts
> WHERE show_id = #URL.show_id#
> </cftransaction>


D
Dan_Bracuk
Inspiring
September 8, 2006
No, it won't work. You need a separate query for each table.
N
Newsgroup_UserAuthor
Inspiring
September 8, 2006
I doubt that will work, but I am not sure.

If it does not you can use the <cftransaction> tag to group all the
deletes together so that they all happen or none of them happen.

<cftransaction>
DELETE FROM Info
WHERE show_id = #URL.show_id#

DELETE FROM Guests
WHERE show_id = #URL.show_id#

DELETE FROM Hosts
WHERE show_id = #URL.show_id#
</cftransaction>
P
paross1
Participating Frequently
September 8, 2006
If you really, REALLY want to do this in a single transaction, you might investigate setting up your database to use cascading deletes on these particular tables. However, tread carefully on this one, as you may cause a catastrophe if you don't have the correct referential constraints enabled in your database, thereby putting you at risk of inadvertently deleting parent records without deleting the children first, etc.

Phil
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices