delete records from table2 that are not in table1

  • Hello, I need to delete records from table2 that are not in table1

    I have a table1 and table2

    table1

    DB, CODE, RATE, MAX(CONV_DATE) AS MaxConv_Date

    FROM table1

    table2

    DB, CODE, RATE, Date

    FROM

    table2

    I need to delete records from table2 that are not in table1

    Thank you

  • To properly assist you with tested T-SQL please post table definition(s), sample data and desired results.

    To do so please click on the first link in my signature block for a method (including T-SQL statements) to assist us to assist you.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Krasavita (3/17/2011)


    Hello, I need to delete records from table2 that are not in table1

    I have a table1 and table2

    table1

    DB, CODE, RATE, MAX(CONV_DATE) AS MaxConv_Date

    FROM table1

    table2

    DB, CODE, RATE, Date

    FROM

    table2

    I need to delete records from table2 that are not in table1

    Thank you

    As Ron mentioned, this is easier with actual DDL and sample data. But, for a quick question, a quick answer to get you on track. You want what's known as an anti-semi join. Left Join where null for the rest of us hacks.

    Left join the table you're looking to delete from to the table that has the reference, and anytime the reference is missing (null), delete that row.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Krasavita (3/17/2011)


    Hello, I need to delete records from table2 that are not in table1

    I have a table1 and table2

    table1

    DB, CODE, RATE, MAX(CONV_DATE) AS MaxConv_Date

    FROM table1

    table2

    DB, CODE, RATE, Date

    FROM

    table2

    I need to delete records from table2 that are not in table1

    Thank you

    EXCEPT will do a fine job on this. Have a look at it in Books Online.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply