SQL Server Performance

Can we use inner join to delete records

Discussion in 'General Developer Questions' started by Jeya prabhu, Oct 14, 2006.

  1. Jeya prabhu New Member


    Hi,
    Table 1

    user_syscode date value1
    1 01-jan-2006 10
    2 05-jan-2006 40

    Table 2
    user_syscode date
    1 01-jan-2006

    Now Have to delete the values in the table 1, but that (user_syscode and date ) combination should be present in the Table 2. That values only be deleted From Table one.
    Jeya prabhu, Oct 14, 2006
    #2

  2. Madhivanan Moderator


    Make sure you have latest backup of the table before trying this

    Delete t1
    from table1 t1 inner join table2 t2
    on t1.user_syscode=t2.user_syscode and t1.datevalue=t2.datevalue

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Oct 14, 2006
    #2

  3. Jeya prabhu New Member

    Thank u very much ya it's working and I execute after the 'begin transaction' statment.
    Jeya prabhu, Oct 14, 2006
    #2

  4. vindicator New Member

    In addition to Madhivanan's excellent suggestion, SQL 2005 provides one additional sytax that is more readable (for some)
    Delete From TableA
    From TableA InnerJoin TableB
    On TableA.CriteriaA = TableB.CriteriaA
    and TableA.CriteriaB = TableB.CriteriaB
    Note the From clause is repeated.
    vindicator, Feb 19, 2008
    #3

  5. Madhivanan Moderator

    [quote user="vindicator"]
    In addition to Madhivanan's excellent suggestion, SQL 2005 provides one additional sytax that is more readable (for some)
    Delete From TableA
    From TableA InnerJoin TableB
    On TableA.CriteriaA = TableB.CriteriaA
    and TableA.CriteriaB = TableB.CriteriaB
    Note the From clause is repeated.
    [/quote]
    I think the same would work in SQL Server 2000 as well
    Madhivanan, Feb 19, 2008
    #4

Share This Page