Join in WHERE statement of Delete query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Join in WHERE statement of Delete query

Author

Topic

renecarol
Starting Member

5 Posts

Posted - 2011-08-11 : 11:48:45

Has anyone been able to successfully execute a delete query that has a join in the where statement? If so, can you post an example of the code that worked? I've tried all sorts of code that just will not work. I was working on this before and was able to solve the problem by getting all the information I needed from one table. I had to revisit this issue when I was told I had to automate finding the last day of the month based on the current Year & Month (YrMo) plus the number of days based on what month it is. I created a table to include a few years worth of dates that have YrMo and LastDay (is how many days in that month). All of dates are text fields that are in YYYYMMDD format. If the information is pulled from somewhere that is an actual date field it is converted from #08/10/2011# to "20110810" or from #08/10/11# to "20110810" etc.

I need to delete from the table CovElig the rows that have duplicate MemberIds where the End Date is not equal to "00000000" (infinite end date) and also not equal to CovElig.YrMo+MT_LastDay.LastDay (Where YrMo is a text field YYYYMM and LastDay is a column in the Master Table LastDay that has the last day of the month based on the month). So for August the concatenation of the fields would be "20110831". I also tried "&" instead of "+". Though usually "+" works when I try to concatenate.

Code:

DELETE FROM CovElig IN
(SELECT CovElig.MemberID
FROM MT_LastDay, CovElig
WHERE CovElig.YrMo=MT_LastDay.YrMo
GROUP BY CovElig.MemberID
HAVING COUNT(MemberID)>1))
AND ((CovElig.EndDt_Mem)<>"00000000"
AND (CovElig.EndDt_Mem)<>CovElig.YrMo + MT_LastDay.LastDay));

I was able to get the code to work & do what I wanted it to with this code:
Code:

DELETE *
FROM CovElig
WHERE MemberID in
(SELECT MemberID
FROM CovElig
GROUP BY MemberID
HAVING COUNT(MemberID)>1)
AND EndDt_Mem <> "00000000" AND <> “20110630”;

As I mentioned earlier I was told I couldn't hard code dates in I had to automate it so that it would know the number of days in the month based on the YrMo field, add that day to YrMo & compare against the EndDt_mem field. Which puts me squarely back to where I was when i try to run delete code with a join in the where clause it doesn't know what table to delete from. Eventhough it looks clear to me that DELETE * FROM CovElig (means delete the rows from CovElig). I didn't use any aliases because when code doesn't work I like to be able to clearly see what column is in what table so that I can tell better what I'm linking to what. Once I get it to work I will go back through and add in aliases for the table names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources