MS Access 2010 – How to Subtract Records from a Table Based on Their Existence in Another

deletems-access-2010subquery

I have an Access 2010 table Assignment

key Empl    Number
key Year    Number
key Week    Number
key Proj    Number

It assigns employees to zero or more projects for given work weeks,
thus, all four columns make up the composite key.
I have another table, Import, having identical structure, containing data to be used to update Assignment according to different options.

Option 1
Keep all existing assignments, add completely new imports, and ignore any imports that already exist.

I can manage that using a simple append query:

INSERT INTO Assignment 
SELECT *
FROM Import

So far, so good.

Option 2
As above, except where there's an employee-year-week match between assignment and import, replace the existing assignment with the import.

One approach to achieve that is to first delete all records with an employee-year-week match, then append the imports (as above).
The result should be to replace exact matches as-is, replace partial (employee-year-week) matches with new projects, and add completely new assignments.

I can isolate the matches via this query, called Existing:

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week

How can I delete records from Assignment that are also in Existing?

I've tried variations upon this

DELETE 
FROM Assignment
WHERE IN (Existing)

all resulting in error messages.

By the way, it seems that, if there were such an animal, I could use a MINUS (or DIFFERENCE) query:

Assignment
MINUS
Existing

A minor variation on the approach would be to first delete the records with both an employee-year-week match and a project mismatch, then append the imports, as in Option 1. (Fewer deletions and additions, but same result.)

I can isolate those matches via this query, called Existing_exp:

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week AND a.Proj <> i.Proj

but I'm still left with the task of how to delete records from Assignment that are in Existing_exp!

Best Answer

It seems the answer was hiding in plain sight:

DELETE a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week

DELETE a.*
FROM Import AS i INNER JOIN Assignment AS a 
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week AND a.Proj <> i.Proj

depending on which is most appropriate.

I don't know how I failed to see it first time around!

Related Solutions

Oracle 11g – Best Way to Delete Very Large Recordsets

The logic with 'A' and 'B' might be "hidden" behind a virtual column on which you could do the partitioning:

alter session set nls_date_format = 'yyyy-mm-dd';
drop   table tq84_partitioned_table;

create table tq84_partitioned_table (
  status varchar2(1)          not null check (status in ('A', 'B')),
  date_a          date        not null,
  date_b          date        not null,
  date_too_old    date as
                       (  case status
                                 when 'A' then add_months(date_a, -7*12)
                                 when 'B' then            date_b
                                 end
                        ) virtual,
  data            varchar2(100) 
)
partition   by range  (date_too_old) 
( 
  partition p_before_2000_10 values less than (date '2000-10-01'),
  partition p_before_2000_11 values less than (date '2000-11-01'),
  partition p_before_2000_12 values less than (date '2000-12-01'),
  --
  partition p_before_2001_01 values less than (date '2001-01-01'),
  partition p_before_2001_02 values less than (date '2001-02-01'),
  partition p_before_2001_03 values less than (date '2001-03-01'),
  partition p_before_2001_04 values less than (date '2001-04-01'),
  partition p_before_2001_05 values less than (date '2001-05-01'),
  partition p_before_2001_06 values less than (date '2001-06-01'),
  -- and so on and so forth..
  partition p_ values less than (maxvalue)
);

insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '2008-04-14', date '2000-05-17', 
 'B and 2000-05-17 is older than 10 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('B', date '1999-09-19', date '2004-02-12', 
 'B and 2004-02-12 is younger than 10 yrs, must be kept');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2000-06-16', date '2010-01-01', 
 'A and 2000-06-16 is older than 3 yrs, must be deleted');


insert into tq84_partitioned_table (status, date_a, date_b, data) values 
('A', date '2009-06-09', date '1999-08-28', 
 'A and 2009-06-09 is younger than 3 yrs, must be kept');

select * from tq84_partitioned_table order by date_too_old;

-- drop partitions older than 10 or 3 years, respectively:

alter table tq84_partitioned_table drop partition p_before_2000_10;
alter table tq84_partitioned_table drop partition p_before_2000_11;
alter table tq84_partitioned_table drop partition p2000_12;

select * from tq84_partitioned_table order by date_too_old;

Mysql – remove duplicate rows in thesql table that does not contain primary key

In the spirit of @yercube's answer, I have an answer that has an added twist.

CREATE TABLE stage
(
    id int not null auto_increment,
    name varchar(20),
    primary key (id)
);
CREATE TABLE stage2 LIKE stage;
INSERT INTO stage (name) SELECT name FROM item;
INSERT INTO stage2 (id) SELECT min_id FROM
(SELECT MIN(id) min_id,name FROM stage GROUP BY name) A;
UPDATE stage2 A INNER JOIN stage B USING (id) SET A.name=B.name;
TRUNCATE TABLE item;
INSERT INTO item (name) SELECT name FROM stage2;
DROP TABLE stage;
DROP TABLE stage2;

This will load stage2 with the first occurrence of each name from item, zap the item table, and load the unique occurrences back.

If you look back in @yercube's answer and compare it to my answer, his is much more simplistic because

@yercube uses one temp table, while I use two
I had to create a column for iteration control, @yercube did not need to
@yercube has fewer steps
both answers achieve the same thing

I do not expect my answer to be accepted. The sole purpose of my answer was demonstrate that other answers lose the concise clarity needed to solve your problem. Again, hats off to @yercube.

Best Answer

Related Solutions

Oracle 11g – Best Way to Delete Very Large Recordsets

Mysql – remove duplicate rows in thesql table that does not contain primary key

Related Question