Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    Unanswered: tuning a delete statement

    Hi,

    I need to delete the following records (from enrollment_fact):

    Code:
    SELECT
           a."STU_SID", 
           a."SCHOOL_YEAR", 
           a."DATE_SID", 
           a."LOC_SID"
    FROM "dbo"."ENROLLMENT_FEX2" b,
           "dbo"."LOCATION_DIM" c 
           LEFT OUTER JOIN 
           "dbo"."ENROLLMENT_FACT" a 
           on c."LOC_SID" = a."LOC_SID"
    WHERE 
           b."LOC_KEY" = c."LOC_KEY" 
           and 
           a."DATE_SID" between b."MIN_DATE" and b."MAX_DATE"
    This is the approach (excuse the misuse of the concat function, but you get the idea)

    Code:
    DELETE FROM "dbo"."ENROLLMENT_FACT"
          WHERE CONCAT (a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID") 
          IN (
          SELECT DISTINCT CONCAT (a."STU_SID",
                                  a."SCHOOL_YEAR",
                                  a."DATE_SID",
                                  a."LOC_SID"
                                  )
            FROM "dbo"."ENROLLMENT_FEX2" b, 
            "dbo"."LOCATION_DIM" c 
            LEFT OUTER JOIN "dbo"."ENROLLMENT_FACT" a
            ON c."LOC_SID" = a."LOC_SID"
            AND a."DATE_SID" BETWEEN b."MIN_DATE" 
            AND b."MAX_DATE")
    comments? better way? (without using an sp)

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you use LEFT OUTER in the first query -- why?!

    LEFT OUTER is supposed to return rows from the left table, even if they have no matching rows in the right table, and in this case the ENROLLMENT_FACT table is the right table!!

    so it's not at all clear which rows you want to delete
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105
    whoops...wrong table!!!

    need to deleting from "dbo"."ENROLLMENT_FEX2" b.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and which rows would you like to delete from that table? perhaps try stating it in words
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105
    umm....where the rows in ENROLLMENT_FEX2 have the combination of

    a."STU_SID",
    a."SCHOOL_YEAR",
    a."DATE_SID",
    a."LOC_SID"

    returned from the previous query.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which previous query?

    i'm sorry, but you have to be a bit more specific

    perhaps try stating it in words
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105
    this query....

    Code:
    SELECT
           a."STU_SID", 
           a."SCHOOL_YEAR", 
           a."DATE_SID", 
           a."LOC_SID"
    FROM "dbo"."ENROLLMENT_FEX2" b,
           "dbo"."LOCATION_DIM" c 
           LEFT OUTER JOIN 
           "dbo"."ENROLLMENT_FACT" a 
           on c."LOC_SID" = a."LOC_SID"
    WHERE 
           b."LOC_KEY" = c."LOC_KEY" 
           and 
           a."DATE_SID" between b."MIN_DATE" and b."MAX_DATE"
    returns

    Code:
     a."STU_SID", 
     a."SCHOOL_YEAR", 
     a."DATE_SID", 
     a."LOC_SID"
    I need to delete these particular records from ENROLLMENT_FEX2 which have this compound primary key. (i.e. stu_sid + school_year + date_sid + loc_sid)...i.e. the same as the query.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, that's just not doing it for me

    you want to delete from one table where some arbitrary query returns rows from another table that may or may not be related to the first table in a left outer join?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105
    i'll work on that.

    by the by...yes, i agree the SELECT didn't make all that much sense....it was given to me by a user which in turn was the SQL generated by Crystal Report....(so perhaps a meta-data problem in his reporting tool...) with the instructions, "make these records go away...now!"

    problem solved.

    thanks.

Posting Permissions

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