August 11, 2011 at 10:55 am
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.
August 11, 2011 at 6:44 pm
Hi
When you say it doesn't work with a join in your subquery, do you mean it errors or simply does not result in any records being deleted?
If you are getting an error from the query, posting it here would help to work out what is going on.
Otherwise, I'm no SQL guru, but I know subqueries which include joins do work (I use them regularly), and I know of no limitations on the use of such a query in a delete query (although I don't recall a situation in which I have used one). If one of the experts who cruise these forums has any knowledge to the contrary, I'm here to learn as well so I'm happy to be enlightened 🙂
My first thoughts would be towards checking that the subquery is actually returning MemberIDs which should be deleted - so run it as a normal query and do a sample check. Maybe also convert the joins to use INNER JOIN syntax rather than the equijoin syntax you are currently using. If you need help with that syntax (I have no idea as to your level of SQL expertise) I am more than happy to offer some guidance.
Finally, it might be a good idea to let us know what version of SQL Server you are using - just in case this is a version specific issue.
Cheers
Rowan
August 12, 2011 at 7:14 am
I'm sorry I thought i posted the error. It says "specify the table containing the records you wish to delete". I want them to delete from CovElig. I just don't know how to specify any more clearly than DELETE FROM CovElig.
August 12, 2011 at 7:17 am
Changing DELETE * FROM CovElig to SELECT * FROM CovElig returns the records I want to delete. Its like I can't have a join in the where clause for a delete query.
August 12, 2011 at 12:13 pm
The error message indicate the problem quite clear:
Try DELETE CovElig.* FROM ...
August 12, 2011 at 12:17 pm
You're a genius!!! Thank you so much. That worked and deleted all the records I need it to.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply