Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    37

    Unanswered: Deleting Fields from a table

    Hi

    I am having a really bad week here and I cannot even do the simplest of things. I am trying to delete some records from a table.

    My query in sql is this:

    DELETE [(SE)_tbl_Exchange_Rates].Month, [(SE)_tbl_Exchange_Rates].CODE, [(SE)_tbl_Exchange_Rates].COUNTRY, [(SE)_tbl_Exchange_Rates].RATE
    FROM [(SE)_tbl_Exchange_Rates] INNER JOIN [Holding Table] ON ([(SE)_tbl_Exchange_Rates].Month = [Holding Table].Month) AND ([(SE)_tbl_Exchange_Rates].CODE = [Holding Table].CODE);

    Basically I am saying delete record from table "exchange rates" if there is an identical record already in the Holding table. A record is considered identical if the month and the code are the same.

    When I run the query I get the following error message:"specify the table containing the records you want to delete.

    Help! this is driving me crazy!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sounds like you are confusing fields and records, more properly known as columns and rows. You use DML queries to delete rows, you use DDL instructions to drop columns. Here, you are using DML to delete rows. As such, you don't specify column names.

    Code:
    DELETE [(SE)_tbl_Exchange_Rates].*
    FROM [(SE)_tbl_Exchange_Rates] INNER JOIN [Holding Table] ON ([(SE)_tbl_Exchange_Rates].Month = [Holding Table].Month) AND ([(SE)_tbl_Exchange_Rates].CODE = [Holding Table].CODE);
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Posts
    37
    You are right - I confused records and fields in my heading. I obviously mean records i.e. rows of data I want to delete and not columns. I have tried the code you supplied previously but I get the following error message:Could not delete from specified table

    The table is not open and it is not read only.....

    what is going on.....

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    try:
    Code:
    DELETE *
    FROM [(SE)_tbl_Exchange_Rates]
    WHERE EXISTS (SELECT NULL FROM [Holding Table] WHERE [(SE)_tbl_Exchange_Rates].[Month] = [Holding Table].[Month] AND [(SE)_tbl_Exchange_Rates].[code ] = [Holding Table].[code ])
    NOTE - I have had to add a space in [table name].[code ] as code is a reserved word here.
    Probably a reserved word in Access too. Month certainly is...
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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