delete unrelated records

[xsnrg]

I know I'm missing something basic cause I've done this tons of times and must be missing something.

I have deleted archived projects from a table and now want all tasks that no longer have a related record in the projects table to be deleted as well... tried this but am missing something, won't work... says "can't delete from specified table"

DELETE T.*, W.WorkspaceID
FROM dbo_TaskItem AS T LEFT JOIN dbo_Workspace AS W ON T.ParentWorkspaceID = W.WorkspaceID
WHERE (((W.WorkspaceID) Is Null));

What did I miss???

 

[PHV]

Something like this ?
DELETE FROM dbo_TaskItem
WHERE ParentWorkspaceID NOT IN
(SELECT DISTINCT WorkspaceID FROM dbo_Workspace);


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

 

[xsnrg]

Doesn't seem to like that code. I think the data must be too large for the query within a query option. I've done it with the join method before no problem.

Any other ideas how this can work.

 

[PHV]

Doesn't seem to like that code
Any error message ?

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

 

[xsnrg]

No error... just trying real hard to perform. After 15 minutes or more, it still wasn't even 20% of the way done according to the status bar. I had to end task. So I know the code works cause no error... but it must not be good for this large of a data set.

The Project table is a linked table in another Access database that is 50MB and the Task table alone is over 119Mb. very large data sets.

 

[xsnrg]

Discovered the problem - Primary Key...
Both our codes work fine when the related table (and the field that is joind) is a primary key. For some reason my import script has killed the PK identifier. Now I have a different challenge to fix.

Thanks for your help...

 

[PHV]

Perhaps something like this ?
DELETE FROM dbo_TaskItem LEFT JOIN dbo_Workspace
ON ParentWorkspaceID = WorkspaceID
WHERE WorkspaceID Is Null;

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

 

[xsnrg]

Tried your code to see if this would get around my PK issue. No luck...

I get this error...

McLean Jones
buddycenters.org
"Believing is Seeing

 

[willir]

Okay Gentlemen

I don't understand this...

DELETE T.*, W.WorkspaceID
FROM dbo_TaskItem AS T LEFT JOIN dbo_Workspace AS W ON [COLOR=red]T.ParentWorkspaceID = W.WorkspaceID[/color]
WHERE [COLOR=blue](((W.WorkspaceID) Is Null))[/color];

How can...
T.ParentWorkspaceID = W.WorkspaceID
And...
W.WorkspaceID be Null ???;

...Would this ever happen? - ID's equal (true) and ID is null (true)


I would suggest you consider going back to PHV's first suggestion...
- Ensure / create indexes on WorkspaceID for both tables
- Run PHV's SQL statement...[tt]
DELETE FROM dbo_TaskItem
WHERE ParentWorkspaceID NOT IN
(SELECT DISTINCT WorkspaceID FROM dbo_Workspace);[/tt]

Richard

 

[PHV]

willir, take a look at how works OUTER joins.

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

 

[xsnrg]

the primary key is the issue here. I'm now looking to find out how I can either maintain the indexes on the import or recreate them after the import.

See thread705-884378


McLean Jones
buddycenters.org
"Believing is Seeing