Delete query gone bad

[scottshiv]

I am trying to delete records in [Tile Undup work] based on SSN match to SSN in [Title SSN Match]. Access will let me delete all of the records but when I add the join I get an error that says Could not delete from specified tables. What am I doing wrong?


DELETE [Tile Undup work].*
FROM [Tile Undup work] INNER JOIN [Title SSN Match] ON [Tile Undup work].SSN = [Title SSN Match].[Title SSN];

 

[lespaul]

how about:

delete from [Tile Undup work] where SSN in (SELECT [Title SSN] FROM [Title SSN Match])

Leslie

Have you met Hardy Heron?

 

[scottshiv]

I stumbled across this and it worked.

1. Open the properties window on the query in design view.

2. If the 'Unique Records' property is set to No. set it to yes.

I did the above and my query worked.

Leslie, thank you for the alternative solution.

 

[PHV]

In fact, your SQL should now be:
DELETE [!]DISTINCTROW[/!] [Tile Undup work].*
FROM [Tile Undup work] INNER JOIN [Title SSN Match] ON [Tile Undup work].SSN = [Title SSN Match].[Title SSN];

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886