SQL Update

[Darryl Kerkeslager]

How would I implement the WHER clause of this SQL?

UPDATE address
SET add_current=Tru e

WHERE (SELECT address_id, add_off_id, add_start_date FROM address as ATable
WHERE count (add_off_id) >1)
add_start_date is the newest date
IOW, I added the add_current field to my table because I found users were
lazily just putting any old start date in, so sorting by start date was
unreliable, so now I've forced a "current address" flag, but need to set as
many newest addresses to current using SQL as I can (of course, the
duplicate start dates will have to be done by hand).
--
Darryl Kerkeslager

[teddysnips]

Darryl Kerkeslager wrote:

How would I implement the WHER clause of this SQL?

UPDATE address
SET add_current=Tru e

WHERE (SELECT address_id, add_off_id, add_start_date FROM address as ATable
WHERE count (add_off_id) >1)
add_start_date is the newest date
IOW, I added the add_current field to my table because I found users were
lazily just putting any old start date in, so sorting by start date was
unreliable, so now I've forced a "current address" flag, but need to set as
many newest addresses to current using SQL as I can (of course, the
duplicate start dates will have to be done by hand).

I'm not entirely sure what you mean, but you could try something like
this:

SELECT TOP 1 address_id, add_off_id, add_start_date
FROM address
GROUP BY address_id, add_off_id, add_start_date
HAVING (count (address_id) >1)
ORDER BY add_start_date DESC;

HTH

Edward

[Darryl Kerkeslager]

> Darryl Kerkeslager wrote:

How would I implement the WHER clause of this SQL?

UPDATE address
SET add_current=Tru e

WHERE (SELECT address_id, add_off_id, add_start_date FROM address as
ATable
WHERE count (add_off_id) >1)
add_start_date is the newest date
IOW, I added the add_current field to my table because I found users were
lazily just putting any old start date in, so sorting by start date was
unreliable, so now I've forced a "current address" flag, but need to set
as
many newest addresses to current using SQL as I can (of course, the
duplicate start dates will have to be done by hand).

<te********@hot mail.com> wrote I'm not entirely sure what you mean, but you could try something like
this:

SELECT TOP 1 address_id, add_off_id, add_start_date
FROM address
GROUP BY address_id, add_off_id, add_start_date
HAVING (count (address_id) >1)
ORDER BY add_start_date DESC;

Running this query returns no records. The HAVING clause should be:
HAVING (count (add_off_id) >1)

but that also returns no records. This seems to be approximately what I
want (except I would turn it into an Update query to set the add_current
field to True), but again, this SQL does not return records.


--
Darryl Kerkeslager

[teddysnips]

> <te********@hot mail.com> wrote

I'm not entirely sure what you mean, but you could try something like
this:

SELECT TOP 1 address_id
FROM address
GROUP BY address_id, add_off_id, add_start_date
HAVING (count (address_id) >1)
ORDER BY add_start_date DESC;

Running this query returns no records. The HAVING clause should be:

HAVING (count (add_off_id) >1)

but that also returns no records. This seems to be approximately what I
want (except I would turn it into an Update query to set the add_current
field to True), but again, this SQL does not return records.

Well, I tested it in a table with some records that conformed to what I
think your table holds - e.g. multiple address IDs with differing
dates, and mine returned stacks of data.

Without access to your raw data, I suggest that you start from first
principles, and build up the SQL gradually. You know you want records
where the Count(add_off_i d) > 1 so that's the place to start.

Incidentally, I assumed you'd realise that what I intended with my
SELECT was something like WARNING AIR CODE ALERT

UPDATE address
SET add_current=Tru e
WHERE address_id IN (
SELECT TOP 1 address_id
FROM address
GROUP BY address_id, add_off_id, add_start_date
HAVING (count (address_id) >1)
ORDER BY add_start_date DESC)

HTH

Edward

[Darryl Kerkeslager]

I appreciate the time.

Perhaps I should explain my data, just in case - because I still can't get
it to return any records.

address_id (PK, autonumber)
add_off_id (FK, Long, has duplicates)
add_start_date (date, duplicate)
add_current (Boolean)

so sample data might be

1 256 1/1/2004 False
2 256 12/1/2005 False
3 480 1/1/2004 False
4 550 1/2/2005 False
5 980 4/7/2005 False
6 998 4/7/2005 False
7 998 4/9/2005 False
8 1200 1/1/2003 False
9 1230 1/1/2003 False

I want to set address_id #2 and #7 to True, because they have the same
add_off_id, and the add_start_date is the newest of the add_start_dates for
this add_off_id.
--
Darryl Kerkeslager

[Darryl Kerkeslager]

Well, not set the address_id to True obviously, but the add_current field
for those records.
--
Darryl Kerkeslager