CODEWITHSUNDEEP
sqlms-accessms-access-2010
A D
A D

Reputation: 21

How to delete rows in one table based on matching ids and date comparison

I have two tables: HIRES1 and HIRES2 (there is some duplication in names between the lists).

I am trying to delete rows in HIRES1 when the PERSONALID is also inHIRES2as long as the action date (actstdt) inHires1is before the action start date (actstdt) inHires2`.

NOTE: HIRES1 may have multiple rows for one personalid where some dates are before hires2 dates and some are after hires2 dates (I want to keep them if after the hires2 date).

The code that I wrote below asks me to input the parameter value for hires1.actstdt and hires2.actstdt:

DELETE * 
FROM HIRES1 
  WHERE PERSONALID in 
  (
    SELECT DISTINCT PERSONALID 
    FROM HIRES2
  ) AND HIRES1.ACTSTDT < HIRES2.actstdt;

Upvotes: 2

Views: 1160

Answers (2)

Malachi
Malachi

Reputation: 3221

I looked on Google and found another question similar to this one, Here is the Answer to that Question

it gives a little bit easier way to do this

DELETE HIRES1.*
FROM HIRES1
WHERE EXISTS(SELECT PERSONALID 
             FROM HIRES2 
             WHERE HIRES2.PERSONALID = HIRES1.PERSONALID
                    AND HIRES1.ACTSTDT < HIRES2.actstdt)

****This is not tested****

but it should work.

I think your Query didn't work because you had the Date Range part of the query on the outside of the parenthesis.

Upvotes: 0

HansUp
HansUp

Reputation: 97131

Use DCount to retrieve the number of HIRES2 rows which have the same PERSONALID and a later ACTSTDT as each row in HIRES1. Eventually, you will DELETE those HIRES1 rows for which that count is > 0, but first work out the logic with a SELECT query. Once you have that returning the correct target rows, you can convert it to a DELETE.

If I understood the requirements correctly, this should be a reasonable starting point ...

SELECT *
FROM HIRES1
WHERE
    DCount
        (
            '*',
            'HIRES2',
            'PERSONALID=' & PERSONALID
                & ' AND actstdt>' & Format(ACTSTDT, '\#yyyy-m-d\#')
        ) > 0;

I assumed numeric data type for PERSONALID. If it's actually text, you must add quotes around the value in that DCount expression.

Upvotes: 1

Related Questions