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.
  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
  3. Jeya prabhu New Member

    Thank u very much ya it's working and I execute after the 'begin transaction' statment.
  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.
  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

Share This Page