Results 1 to 13 of 13
  1. #1
    Join Date
    May 2007
    Posts
    27

    Question Unanswered: SQL to Delete Records

    Getting an error with the following:

    DELETE
    FROM Table1 INNER JOIN Table2 ON Table1.FieldName = Table2.FieldName
    Where (((Table2.FieldName) LIKE "criteria"));


    Table 2 contains a primary key that ties the two tables together. It also contains another field which allows me to select specific record types.
    It's a one-to-many with Table1.

    Table 1 contains numeric data that is associated with Table2. No primary key.


    Error Msg is: Specify the table containing the records you want to delete.

    Can someone set me straight?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    DELETE FROM Table1 where Table1.FieldName in (select Table2.FieldName from table2 Where Table2.FieldName LIKE "criteria");

    Andy

  3. #3
    Join Date
    May 2007
    Posts
    27
    Seems to work.

    Thank you

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by myBoo
    DELETE
    FROM Table1 INNER JOIN Table2 ON Table1.FieldName = Table2.FieldName
    Where (((Table2.FieldName) LIKE "criteria"));
    Joins (or views defined on joins) are never updatable, i.e., cannot be the subject of an insert, update or delete.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by Peter.Vanroose
    Joins (or views defined on joins) are never updatable, i.e., cannot be the subject of an insert, update or delete.
    Thanks....

    It seems the previous solution is not the best for my situation. I ran it and it seemed to work but after 24hrs only about 200 records were deleted with no end in sight. I had to force the command to end. Not very efficient.

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    You might try a correlated subquery:

    DELETE
    FROM Table1
    WHERE EXISTS (
    SELECT * FROM Table2
    WHERE Table1.FieldName = Table2.FieldName
    AND Table2.FieldName LIKE "criteria") ;

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Slow deletes are very often due to FK constraints.

    If you can, drop all FK constraints (pointing both to and from the table in question) and recreate the FK's after the delete.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Dropping FK's from this table to others is not necessary. DB2 does not have to check those constraints in case of a DELETE operation.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Dropping FK's from this table to others is not necessary. DB2 does not have to check those constraints in case of a DELETE operation.
    stolze, that would be logic, but I have found out it does influences delete speed dramatically.

    So now I drop all FK's, both defined on the table itself as on other tables that reference the "delete" table. Perhaps the latest DB2 versions behave different, I use this method for years now.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by stolze
    Dropping FK's from this table to others is not necessary. DB2 does not have to check those constraints in case of a DELETE operation.
    I followed the suggestion by ARWinner..... My target table has 5000 records in it.... I'm trying to delete 32 of them.... ran overnight without returning my cursor.

    Will try removing FKs from this table to see what results that provides.

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Wim
    If you can, drop all FK constraints (pointing both to and from the table in question) and recreate the FK's after the delete.
    When putting back the FKs, your tablespace(s) will be put in check pending state, so don't forget to run CHECK DATA after that (at least, on z/OS; no idea what's the equivalent on other platforms)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    DB2 LUW checks the FKs right away - no need for CHECK DATA or - what a closer equivalent would be - the SET INTEGRITY statement.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by myBoo
    I followed the suggestion by ARWinner..... My target table has 5000 records in it.... I'm trying to delete 32 of them.... ran overnight without returning my cursor.

    Will try removing FKs from this table to see what results that provides.
    Wat are your results ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •