JOIN DELETE - SQL

[Newsgroup_User]

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

[Newsgroup_User]

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>

[paross1]

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

[Dan_Bracuk]

No, it won't work. You need a separate query for each table.

[Newsgroup_User]

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>

[Newsgroup_User]

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.

[paross1]

He just needs to make sure that his queries are within three different sets of cfquery tags within the cftransaction.

Phil

[davidsimms]

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

[Newsgroup_User]

So basically keep this idea?

<cfquery name="Remove" datasource="sql_portal">
DELETE FROM RadioShowInfo
Where show_id = #arguments.show#
DELETE FROM RadioShowGuests
Where show_id = #arguments.show#
</cfquery>

[davidsimms]

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

[Guest]

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


Good luck!