June 11, 2013 at 6:58 am
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
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 )
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 11, 2013 at 7:25 am
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
No problem and thanks for posting back.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 11, 2013 at 8:09 am
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
I would add an OUTPUT clause to your DELETE query. Have a look at the examples here.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 11, 2013 at 11:26 am
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