Skip to main content
J
jfb00
Inspiring
October 31, 2011
Answered

delete records question

  • October 31, 2011
  • 3 replies
  • 4362 views

Hi All,

How can I delete records from a table base on a query result?

<cfquery name="newRecords" datasource="#my_str_dsn#">
  select * 
  from TableA 
  where myKey_id= #arguments.key_id# 
</cfquery>
<cfquery name="deleteRecords" datasource="#my_str_dsn#">
  delete from TableB
  where myKey_id in (select myKey_id from newRecords)
</cfquery>

I know i can do this in one statement but I am converting array items into the query result first and then i want to delete those from a table.

Thanks

Johnny

    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    Ok Dan,

    Any example or link in how to do this?

    Thanks


    Concatonating syntax is database specific.  Google will help you find the correct syntax for whatever db you are using.  Cold Fusion Query of Queries uses the same syntax as Oracle.

    3 replies

    A
    Adam Cameron.
    Inspiring
    November 1, 2011

    I'm missing something.  Why are you pulling records down to CF via newRecords, then pushing them back up to the DB for the delete query?  Why don't you just do it all on the DB, eg:

    DELETE [whatever]

    WHERE id IN (

        [the newRecords query's SQL statement]

    )

    ?

    --

    Adam

    Owainnorth
    Owainnorth
    Inspiring
    November 1, 2011

    Adam Cameron. wrote:

    Why don't you just do it all on the DB?

    I think there's more logic going on in the meantime, but I'm not really even sure what the OP is after anymore. Still, as I said earlier there's no reason you couldn't wrap it in a transaction, do a select, do whatever then run a delete with the same clauses. Or possibly use something funky like a RETURNING statement to bring back the deleted data post-DML.

    J
    JMF3
    Participating Frequently
    October 31, 2011

    Just as Owain laid out, but if you don't need to do anything else to the results of the first query, then you can create that list like so:

    <cfquery name="newRecords" datasource="#my_str_dsn#">
      select *
      from TableA
      where myKey_id= #arguments.key_id#
    </cfquery>

    <cfquery name="deleteRecords" datasource="#my_str_dsn#">
      delete from TableB
      where myKey_id in ( <cfqueryparam cfsqltype="cf_sql_varchar" list="true" value="#valueList(newRecords.myKey_id#" /> )
    </cfquery>

    J
    jfb00Author
    Inspiring
    October 31, 2011

    I need more than that:

    My delete statement is like:

    <cfquery name="deleteRecords" datasource="#application.str_dsn#">

                    Delete from tableA A

                    where not exists (select 1 from tableB B

                        where A.key_id = B.key_id

                        and A.keyB_id = B.keyB_id

                        and A.keyC_id = B.keyC_id

                        and A.fy = B.fy )

                </cfquery>

    Thanks

    Owainnorth
    Owainnorth
    Inspiring
    October 31, 2011

    Eh? Now your delete statement doesn't even have any variables in it?

    Owainnorth
    Owainnorth
    Inspiring
    October 31, 2011

    You just need to get the IDs from the first table into a list, then you can pass that list as a parameter to the second query. I take it you do something else with the values before the delete, hence the need to two queries?

    Could you not just put the two inside a transaction so you know the delete will remove the same records you selected earlier?

    J
    jfb00Author
    Inspiring
    October 31, 2011

    Well, my delete statement is more complecate than just ids, I did this because as a simple explanation.

    I need to know how to use the result of the first query inside of a second query, I am getting error: "Complex object types cannot be converted to simple values."

    Thanks for your help!

    Owainnorth
    Owainnorth
    Inspiring
    October 31, 2011

    Fair enough, gotcha.

    As I say, you need to get the values into a list, by whatever means. Look at valuelist(), arrayToList() or even just create a custom list using listAppend() as you presumably loop through the results of your previous query. Either way, make sure you get a STRING which is a comma-delimited list. You can then do this:

    <cfquery>

       DELETE FROM table2

       WHERE id in (<cfqueryparam cfsqltype="cf_sql_varchar" list="true" value="#myIdList#" />)

    </cfquery>

    That should do you. Bear in mind that could be absolutely awful from a tuning/efficiency point of view, depending on how many rows you're talking about.

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