Solved

MS Access - Delete Records In A Table

Posted on 2014-03-11
7
325 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

615 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