Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: simple SQL help please

    this should be easy but i'm stuck...

    two tables tblA, tblB with identical structure
    both tables include a field fMonth (Text like "200312")
    approx 1000 rows for each fMonth.
    tblA and tblB may have any (possibly different) number of fMonth values (max of six years history so max 72 different fMonth values in each table)

    how to use a single SQL statement to delete from tblA all rows that have a value of fMonth that also occurs in tblB

    pseudo-SQL:
    Code:
    DELETE * FROM tblA WHERE tblA.fMonth has a matching fMonth value anywhere in tblB
    i can do it skipping thru a recordset, but it should be possible in SQL

    thanks for your thoughts. izy
    currently using SS 2008R2

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    You need to use an updatable query. Try this:

    DELETE * FROM tblA WHERE tblA.fMonth in (SELECT fMonth FROM tblB);

    It does a full table scan, but if there aren't millions of records, it works well.

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: simple SQL help please

    Originally posted by izyrider
    this should be easy but i'm stuck...

    two tables tblA, tblB with identical structure
    both tables include a field fMonth (Text like "200312")
    approx 1000 rows for each fMonth.
    tblA and tblB may have any (possibly different) number of fMonth values (max of six years history so max 72 different fMonth values in each table)

    how to use a single SQL statement to delete from tblA all rows that have a value of fMonth that also occurs in tblB

    pseudo-SQL:
    Code:
    DELETE * FROM tblA WHERE tblA.fMonth has a matching fMonth value anywhere in tblB
    i can do it skipping thru a recordset, but it should be possible in SQL

    thanks for your thoughts. izy
    Use Select in Select

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    THANKS!

    maybe i was sitting too close to the screen.

    thanks guys, 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
  •