Post reply

DELETE based on one-to-many relationship

  • June 11, 2013 at 6:58 am

    #294457

    I must be having a "senior moment" (no insult intended), because I just can't figure this one out.

    Table A (col1, col2,col3....col27)

    Table B (col1,col2,col3)

    I need to delete all the rows from Table A that have the same column values as Table B.

    Example: Row 1 Table B values (123,stuff, 321) there will be many rows in Table A with these same values in Col1-3, but Columns 4-27 will be different. if the row is found in Table B, I need to delete all the rows in Table A that have those same values in Columns 1-3

    DELETE FROM TableA WHERE EXISTS(results of TableB)

    this deletes the entire table A, not just the rows that have Col1,Col2,Col3 the same.

    SQL Padre
    aka Robert M Bishop

    "Do or do not, there is no try" -- Yoda

  • June 11, 2013 at 7:06 am

    #1622884

    You could try something like this:

    delete a

    from TableA a

    where exists ( select 1

    from TableB b

    where a.col1 = b.col1

    and a.col2 = b.col2

    and a.col3 = b.col3 )

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 11, 2013 at 7:25 am

    #1622912

    THANK YOU! Worked Like a charm! I couldn't figure out the alias for Table A and how to join it to table B.

    Thank you again!

    SQL Padre
    aka Robert M Bishop

    "Do or do not, there is no try" -- Yoda

  • June 11, 2013 at 7:34 am

    #1622926

    No problem and thanks for posting back.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 11, 2013 at 8:09 am

    #1622949

    one more question, please sir.

    if I wanted to build the results of TableB into a table variable to record for audit purposes, i.e. what I am deleting.

    I would build the #TempTable with results then just use that in the join instead of the real table?

    SQL Padre
    aka Robert M Bishop

    "Do or do not, there is no try" -- Yoda

  • June 11, 2013 at 8:34 am

    #1622967

    I would add an OUTPUT clause to your DELETE query. Have a look at the examples here.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • June 11, 2013 at 11:26 am

    #1623068

    Once again, you hit the ball out of the park!

    Thanks!

    SQL Padre
    aka Robert M Bishop

    "Do or do not, there is no try" -- Yoda

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

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