Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL : How to Delete using INNER JOIN in SQL Server ?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 238
    Comment on it

    In this post we will see how to use JOIN for deleting data from SQl server table. Let us first create tables which we will use for understanding the deletion process using JOINS.

    -- Create table1
    CREATE TABLE #Table1 (Col1 INT, Col2 VARCHAR(50))
    INSERT INTO #Table1 (Col1, Col2)
    SELECT 11,'First'
    UNION ALL
    SELECT 12, 'Second'
    UNION ALL
    SELECT 13, 'Third'
    UNION ALL
    SELECT 14, 'Fourth'
    UNION ALL
    SELECT 14, 'Fifth'
    GO
    -- Create table2
    CREATE TABLE #Table2 (Col1 INT, Col2 VARCHAR(50))
    INSERT INTO #Table2 (Col1, Col2)
    SELECT 21, 'TwentyOne'
    UNION ALL
    SELECT 22, 'TwentyTwo'
    UNION ALL
    SELECT 23, 'TwentyThree'
    UNION ALL
    SELECT 14, 'TwentyFour'
    GO
    

    Let us say we want to delete data from #Table1 based on matching values in col1 of both tables. The query will be as below:

    DELETE #Table1
    FROM #Table1 t1
    INNER JOIN #Table2 t2 ON t1.Col1 = t2.Col1
    GO
    

    The above query will delete two rows from #Table1 where col1 value is 14.Additionally we can also use the WHERE clause if we want to narrow down are delete based on values in columns.

    DELETE #Table1
    FROM #Table1 t1
    INNER JOIN #Table2 t2 ON t1.Col1 = t2.Col1
    WHERE t1.Col2 IN ('Fourth')
    GO
    

    The above query will delete one row from #Table1 where col1 value is 14 and Col2 value is 'Fourth'

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: