DELETE SQL Query

Question

Post reply

DELETE SQL Query

SQL Server

Old Hand

Points: 377
More actions
September 24, 2019 at 6:02 pm

#3683020

Hi Folks:
Need help with query. I have process which runs daily. We have these 2 tables TABLE-A and TABLE-B. We would like to get records from TABLE-A deleted whenever the DeleteDate in TABLE-B is equal to Date in TABLE-A.

Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

This seems fairly straightforward. What have you tried and where are you running into problems?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

pietlinden SSC Guru Points: 63503 More actions · Answer 2

DELETE FROM TableA
WHERE EXISTS (SELECT 1 
     FROM TableB 
     WHERE TableB.DeleteDate = TableA.RecDate);

?

I guess I should have asked what you tried.

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 3

Considering the content of the delete table, I'm thinking that you also need to include the ID in the delete criteria.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

allan.ford17 SSC Eights! Points: 944 More actions · Answer 4

As per Jeff's comment:As per Jeff's comment:

DELETE FROM TableA
WHERE EXISTS (SELECT 1
FROM TableB
WHERE TableB.DeleteDate = TableA.RecDate and TableB.ID = TableA.ID);

Because of duplicates in TableA on ID and RecDate, the delete will delete more (12) records from TableA.

This reply was modified 5 years, 2 months ago by allan.ford17.