Post reply

Delete help

  • May 19, 2009 at 7:48 am

    #134430

    I have a table with two columns, FromDate and ThruDate. I want to delete records from a second table that has a DueDate, where the DueDate is between the FromDate and ThruDate of first table.

    I am struggling with how to get this done without using a cursor.

    Thanks in advance,

    vmon

  • May 19, 2009 at 8:11 am

    #996997

    How about adding some table structure, sample data & the required output? Don't know how to do that click here[/url]

    --Ramesh

  • May 19, 2009 at 8:29 am

    #997026

    tblRange

    RecNumId int

    FromDate datetime

    ThruDate datetime

    tblInvoice

    RecNumId int

    DueDate

    DELETE FROM tblInvoice

    WHERE tblInvoice.DueDate BETWEEN tblRange.FromDate AND tblRange.ThruDate

    I am not sure how to get the delete to remove records from one table based on a column in a second table.

    Thanks for your help. Sorry I was lacking in supporting information on first post.

    vmon.

  • May 19, 2009 at 8:35 am

    #997036

    The syntax of DELETE on a JOIN can be tricky.

    For example, suppose you have Table1 and Table2 joined on Primary Key From Table1 to Foreign Key on Table2

    DELETE FROM T1

    FROM Table1 AS T1

    JOIN Table2 AS T2

    ON T1.pKey = T2.fKey

    WHERE (...conditions of delete...)

  • May 19, 2009 at 8:36 am

    #997038

    Check if the below query works for you.

    DELETE

    FROM dbo.tblInvoice I

    WHERE EXISTS( SELECT * FROM dbo.tblRange R WHERE I.DueDate BETWEEN R.FromDate AND R.ThruDate )

    Edit:

    You have not included "RecNumId" in joins in your query posted previously. I assuming that it has to be linked, correct?

    --Ramesh

  • May 19, 2009 at 8:41 am

    #997047

    You just need to be careful when using BETWEEN since SQL Server stores the Date with a time so you need to take the time into account. For example

    Delete From table Where DueDate Between '1/1/2009' and '1/8/2009'

    Will delete all rows where the DueDate is on or after 1/1/2009 00:00:00.000 and on or before 1/8/2009 00:00:00.000 so that a row with 1/8/2009 00:00:01.000 will NOT be deleted.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 6 posts - 1 through 5 (of 5 total)

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