Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

MS Access - Delete Records In A Table

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
Meritex
Asked:
Meritex
  • 4
  • 3
1 Solution
 
Joe JenkinsCommented:
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
 
MeritexAuthor Commented:
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
 
Joe JenkinsCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
MeritexAuthor Commented:
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
 
Joe JenkinsCommented:
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
 
MeritexAuthor Commented:
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
 
Joe JenkinsCommented:
Glad to help!  Have a great week!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now