10 Replies Latest reply on Sep 12, 2006 11:11 AM by <newbie />

    JOIN DELETE - SQL

    Level 7
      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


        • 1. Re: JOIN DELETE - SQL
          Level 7
          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>
          • 2. Re: JOIN DELETE - SQL
            Dan Bracuk Level 5
            No, it won't work. You need a separate query for each table.
            • 3. JOIN DELETE - SQL
              paross1 Level 2
              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
              • 4. Re: JOIN DELETE - SQL
                Level 7
                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>


                • 5. Re: JOIN DELETE - SQL
                  Level 7
                  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.
                  • 6. Re: JOIN DELETE - SQL
                    paross1 Level 2
                    He just needs to make sure that his queries are within three different sets of cfquery tags within the cftransaction.

                    Phil
                    • 7. Re: JOIN DELETE - SQL
                      davidsimms Level 1
                      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
                      • 8. Re: JOIN DELETE - SQL
                        Level 7
                        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>


                        • 9. Re: JOIN DELETE - SQL
                          davidsimms Level 1
                          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
                          • 10. Re: JOIN DELETE - SQL
                            <newbie /> Level 1

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


                            Good luck!