Delete Subquery? 1

[Trudye]

HI Everyone, got a question;

Is it possible to do a delete subquery. I wrote the following and got a msg Illegal Operation. Wasn't sure weather it was my ignorance of SQL or not possible to do.

strSQL = "DELETE from dealdetailtracking where LINeitemid in (SELECT Delete_it from [deal detail] where delete_it = " & -1 & ")"

Not a big whoop, I re-coded it anyway just wanted to know for future projects.

Thanks much,
Trudye

 

[LonnieJohnson]

Is Delete_it a true/false? Just asking since you have assigned it a value of -1.

Also why would you say IN the values of Delete_It when Delete_It is going to be -1? Why can't you just say where LINeitemid = -1 since the result of your subquery is going to be -1? Again this is evident because the field you are testing in the subquery and the where of the subquery are the same field, so if is where the field equals -1, then the field selected will only equal -1.

Am I missing something?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[PHV]

I guess something like this:
strSQL = "DELETE dealdetailtracking.* from dealdetailtracking INNER JOIN [deal detail] ON dealdetailtracking.LINeitemid=[deal detail].LINeitemid where =[deal detail].delete_it = -1"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trudye]

Thanks so much Lonnie for responding. I structured the SQL statement that way because as I said in my original thread "I don't know what the heck I'm doing"

PHV as always you come to my rescue. Your SQL statement seems more than reasonable I will try it and let ya Know.

Be well,
Trudye