Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2008
    Posts
    16

    Unanswered: delete statement

    Below i will show my database tables relationships. What i am trying to do is delete all where round_number = 1 and event_name = 100M Run, but i cant seem to get it to work. This is what i am attempting
    Code:
         String sql = "DELETE * FROM tblCompetitor " +
    	"SELECT c.ID, r.ID, e.ID " +
    	"FROM tblCompetitor AS c, tblRound AS r, tblEvent AS e " +
    	"WHERE r.Round_Number = ? And e.Event_Name = ?";
    Ignore the syntax, this is for a java program using a prepared statement. My relationships are as follows, any help woiuld be greatly appreciated
    cheers
    http://i33.tinypic.com/2gws12d.jpg

  2. #2
    Join Date
    Aug 2008
    Posts
    16
    Any advice?

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Construct a Delete query in the query designer in MSAccess on the table you want to delete. Then change the query designer (ie upper left button) to show the SQL view. Use that SQL view to help you construct your syntax.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Aug 2008
    Posts
    16
    I tried doing that, and this was the result (changed slightly for java)
    Code:
        String sql = "DELETE tblCompetitor.ID, tblCompetitor.Nationality, tblCompetitor.First_Name, tblCompetitor.Last_Name " +
             "FROM (tblCompetitor INNER JOIN tblRound ON tblCompetitor.ID = tblRound.ID) INNER JOIN tblEvent ON tblCompetitor.ID = tblEvent.ID " +
             "WHERE (((tblRound.Round_Number)=?) AND ((tblEvent.Event_Name)=?))";
    But it tells me to specify the table that needs deleting, but this is what the wizard gave me. How can i fix this?

  5. #5
    Join Date
    Aug 2008
    Posts
    16
    Any further advice? I'm scared that i am soon to jump out the window, lol

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You probably need to change the wording "tblCompetitor" to whatever table name you are actually using. I'm guessing if it's asking you this, it doesn't recognize that tblCompetitor is the table it's supposed to delete from (or it can't find it.)

    Or it could be tblEvent which is the wrong name.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe something like this will isolate DELETE problems from SELECT problems:
    Code:
    DELETE * FROM tblCompetitor 
    WHERE tblCompetitor.ID 
    IN 
    (SELECT tblCompetitor.ID
    FROM (tblCompetitor INNER JOIN tblRound ON tblCompetitor.ID = tblRound.ID INNER JOIN tblEvent ON tblCompetitor.ID = tblEvent.ID WHERE tblRound.Round_Number=1 AND tblEvent.Event_Name='100M Run');
    ...i don't cascade deletes (and you dont mention if you do). i would first need to delete any records using these IDs as FK.

    izy
    currently using SS 2008R2

Posting Permissions

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