Delete Query

[Delindan]

I am trying to create a delete query and having trouble with syntax. I have two tables, pending and jobs. I would like to delete all the records from pending meeting the criterea pending.position = jobs.position AND pending.new/replace='R' AND pending.req#=jobs.req#

I tried creating a select query and using the design view (which worked) and then turning into a delete query which didn't work. The select query used an innerjoin which I don't know or understand how it works. Any thoughts?

 

[Andrzejek]

Could you post your SELECT statement that worked?
And let us know WHAT do you want to DELETE?

Have fun.

---- Andy

 

[Delindan]

As you can see, I abbreviated the names however the query is still basically the same.

SELECT [Pending Detail].[Req #], [Pending Detail].[Position Number], [Pending Detail].[Cost Center for Salary], [Pending Detail].[Req# Employee Status], [Pending Detail].[Target Start Date], [Pending Detail].[New/Replace]
FROM [Pending Detail], [Job Activity]
WHERE ((([Pending Detail].[Req #])=[Job Activity]![Taleo Number]) AND (([Pending Detail].[Position Number])=[Job Activity]![Position Number]) AND (([Pending Detail].[New/Replace])="R"));

When I turn it into a delete query, it asks me to specify the table I want the record deleted from. It seems obvious it would be Pending Detail but I guess not. Thanks

 

[Delindan]

I got the select query simplified even more....


Select *
From [Pending Detail], [Job Activity] WHERE
[Job Activity].[Position Number] = [Pending Detail].[Position Number] AND [Job Activity].[Taleo Number] = [Pending Detail].[Req #] AND [Pending Detail].[New/Replace]='R';

Again, still having trouble turning it into a delete query

 

[Delindan]

I got it to work...using some other example I found out here!!

DELETE * FROM [Pending Detail]
WHERE [Pending Detail].[Position Number] in

(SELECT [Pending Detail].[Position Number]
FROM [Pending Detail] INNER JOIN [Job Activity] ON [Pending Detail].[Position Number] = [Job Activity].[Position Number]
WHERE [Pending Detail].[Req #] = [Job activity].[Taleo Number] AND [Pending Detail].[New/Replace] = 'R');

 

[Andrzejek]

You can DELETE from one table only (I think).

I re-did you select to:

SELECT D.*
FROM [Pending Detail] D, [Job Activity] A
WHERE A.[Position Number] = D.[Position Number] 
  AND A.[Taleo Number] = D.[Req #] 
  AND D.[New/Replace] = 'R';

Because I can not stand spaces in table names or in field names, but that's just me.

But, how about this:

DELETE FROM [Pending Detail]
WHERE [Pending Detail].[Position Number] in[blue](
SELECT [Job Activity].[Position Number] 
FROM [Job Activity], [Pending Detail]
WHERE [Job Activity].[Position Number] = [Pending Detail].[Position Number] 
AND [Job Activity].[Taleo Number] = [Pending Detail].[Req #])[/blue]
AND [Pending Detail].[New/Replace]='R';

This is just my guess, but this way you deal with (pretty much) just one table: [Pending Detail], which should work.


Have fun.

---- Andy

 

[Delindan]

Thanks for the suggestion. It would definately make life easier to have no spaces in names. I guess it's one of those things I learn the hard way. I appreciate the hint on getting rid of the spaces

 

[Andrzejek]

You've got almost exactly the same Delete statement
Great minds work alike

Instead of Spaces you can use underscore:
[tt]
SELECT Pending_Detail.Position_Number ...[/tt]


Have fun.

---- Andy