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.
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.
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>
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.
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.
<cfquery name="Remove" datasource="sql_portal">
DELETE FROM RadioShowInfo
Where show_id = #arguments.show#
DELETE FROM RadioShowGuests
Where show_id = #arguments.show#
</cfquery>
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."
10
Replies
AdChoices