Solved

MS Access - Delete Records In A Table

Posted on 2014-03-11
7
323 Views
Last Modified: 2014-03-12
Hi Experts -

Been a while since I wrote any delete queries - and its definitely causing me problems.

I have two tables:
Table 20 contains the universe of all of my records -
Table 15 contains those records I want to delete in Table 20 -

My select query works fine -
When I convert the query to a delete query - I get an error "Could not delete from specified tables".

SQL is attached - however I think this is pretty simple and I should just be able to do this by connecting the two tables, right?

Thanks for your help!
SQL.docx
0
Comment
Question by:Meritex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 5

Expert Comment

by:Joe Jenkins
ID: 39921944
You should be able to accomplish this fairly easily.  Assuming the data is the same in each table and column1 is an index between the two tables:

DELETE FROM 020
WHERE EXISTS
   (SELECT 1 FROM 015
        WHERE020.column1 = t=015.column1)

Open in new window

You will need to know an ID column that matches the same records on both tables and adjust the query to a unique column that matches.

Always take a backup before running  a delete query like this to make sure you're good to go.
0
 

Author Comment

by:Meritex
ID: 39921991
I definitely have a backup table.

However, there's no unique identifier ID field to both tables.

The records are unique - and match between tables - but there's no identifier.
0
 
LVL 5

Expert Comment

by:Joe Jenkins
ID: 39922007
So is there no reference point between the two tables that you could use to reference?

Example:

[Company table] (you want to delete from)
company_id
company_name
company_address1
company_address2
company_city
company_state

[table with list of records to delete]
company_id
some_other_column1
some_other_column2
some_other_column3

Both tables share that company_id column.  You need some reference between the tables to make this work.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Meritex
ID: 39922079
The unique records are combinations of various columns - not just one column.

Its the combination of columns 1-10 that create a unique record.

Using your example -
Company table could have company_id 1,2,3,4,5,6,7 etc -
Delete table could also have company id 1,2,3,4,5,6,7 etc -

Its when the combination of:
company_id
company_name
company_address1
company_address2
company_city
company_state
are identical in both tables that I want to delete the record in the company table.
0
 
LVL 5

Accepted Solution

by:
Joe Jenkins earned 500 total points
ID: 39922088
OK, then using the logic in my previous query:

DELETE FROM 020
WHERE EXISTS
   (SELECT 1 FROM 015
        WHERE 
            020.column1 = t=015.column1
            AND 020.column7 = t=015.column7
            AND 020.column2 = t=015.column2
            AND 020.column5 = t=015.column9)

Open in new window

As long as you are matching up the correct columns, you should be good to go!
0
 

Author Comment

by:Meritex
ID: 39923256
Took some syntax guesswork - but got it!

I had my tables flipped in my SQL (015 = 020 instead of 020 = 015) -

Thanks for your help - GREAT walk thru!
0
 
LVL 5

Expert Comment

by:Joe Jenkins
ID: 39923980
Glad to help!  Have a great week!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question