Can't construct correct UPDATE script

I messed up an insert script somewhere and need to correct my data. The tabl
e which needs fixing is:
Table Mailings
AddressID int
PersonID int
NextMailDate datetime
Sample data:
AddressID PersonID NextMailDate
123 4 03/10/2005
123 4 08/30/2005
123 4 12/30/2005
123 12 07/05/2005
Data should be:
123 4 03/10/2005
123 4 NULL
123 4 NULL
123 12 07/05/2005
I need to set NextMailDate to NULL for each PersonID but the max(PersonID) h
aving a date > than the
system date.
I am lost, as you can see from my lame attempt. Thanks, Lars
update mailings
set nextmaildate = null
where addressid = (SELECT AddressID FROM Mailings m ) and
personid <> (SELECT max(PersonID) FROM Mailings WHERE AddressID = m.addressi
d) and
nextmaildate >= convert(nvarchar, getdate(), 112)larzeb wrote:
> I messed up an insert script somewhere and need to correct my data.
> The table which needs fixing is:
> Table Mailings
> AddressID int
> PersonID int
> NextMailDate datetime
> Sample data:
> AddressID PersonID NextMailDate
> 123 4 03/10/2005
> 123 4 08/30/2005
> 123 4 12/30/2005
> 123 12 07/05/2005
> Data should be:
> 123 4 03/10/2005
> 123 4 NULL
> 123 4 NULL
> 123 12 07/05/2005
> I need to set NextMailDate to NULL for each PersonID but the
> max(PersonID) having a date > than the system date.
>
What the PK on this table? I'm not sure I understand the requirement.
Are you saying you want to set the NextMailDate to NULL if a Person has
a NextMailDate > System Date? Are you showing us dates in European
format? I see a 03/10/2005, which in the USA is March 10th (presumably
less than the system date).
Based on the sample data, it looks as though you want a NULL
NextMailDate for all dates for a person that are not equal to the
MIN(NextMailDate).
Could you clarify?
David Gugick
Imceda Software
www.imceda.com|||David,
Sorry for the incomplete explanation. Yes, the dates are mm/dd/yyyy.
I did not include the PK. It is an identity column. The AddressID is a forei
gn key into the Address
table and the PersonID is a foreign key into the Person table.
For a single AddressID, I need to identify the MAX(PersonID) so that the com
bination of AddressID
and MAX(PersondID) is not affected.
For all other combinations of any single AddressID and PersonIDs, I need to
make the NextMailDate
NULL where the existing NextMailDate is equal to or greater than the system
date.
The data illustration was for a single AddressID.
Thanks, Lars
On Mon, 23 May 2005 14:52:24 -0400, "David Gugick" <davidg-nospam@.imceda.com
> wrote:

>larzeb wrote:
>What the PK on this table? I'm not sure I understand the requirement.
>Are you saying you want to set the NextMailDate to NULL if a Person has
>a NextMailDate > System Date? Are you showing us dates in European
>format? I see a 03/10/2005, which in the USA is March 10th (presumably
>less than the system date).
>Based on the sample data, it looks as though you want a NULL
>NextMailDate for all dates for a person that are not equal to the
>MIN(NextMailDate).
>Could you clarify?|||Firstly, don't depend on Identity columns as a guarantee of entry sequence.
You
should instead add a column called DateTime column that marks when the row w
as
added.

> For a single AddressID, I need to identify
> the MAX(PersonID) so that the combination of AddressID
> and MAX(PersondID) is not affected.
This logic does not appear to match your sample results. Given your
requirements, I would expect the following results
AddressId PersonId NextMailDate
123 4 2005-03-10
123 4 Null
123 4 Null
123 12 Null
If this is not the case, then why should the last entry for a given AddressI
d
and PersonId but a date greater than the system date not get reset?
Thomas
"larzeb" <larzeb@.community.nospam> wrote in message
news:627491d56eo3jrbu313505o318e1kn3bh3@.
4ax.com...
>I messed up an insert script somewhere and need to correct my data. The tab
le
>which needs fixing is:
> Table Mailings
> AddressID int
> PersonID int
> NextMailDate datetime
> Sample data:
> AddressID PersonID NextMailDate
> 123 4 03/10/2005
> 123 4 08/30/2005
> 123 4 12/30/2005
> 123 12 07/05/2005
> Data should be:
> 123 4 03/10/2005
> 123 4 NULL
> 123 4 NULL
> 123 12 07/05/2005
> I need to set NextMailDate to NULL for each PersonID but the max(PersonID)
> having a date > than the
> system date.
> I am lost, as you can see from my lame attempt. Thanks, Lars
>
> update mailings
> set nextmaildate = null
> where addressid = (SELECT AddressID FROM Mailings m ) and
> personid <> (SELECT max(PersonID) FROM Mailings WHERE AddressID =
> m.addressid) and
> nextmaildate >= convert(nvarchar, getdate(), 112)|||On Mon, 23 May 2005 12:44:51 -0700, larzeb wrote:
>David,
>Sorry for the incomplete explanation. Yes, the dates are mm/dd/yyyy.
>I did not include the PK. It is an identity column. The AddressID is a fore
ign key into the Address
>table and the PersonID is a foreign key into the Person table.
>For a single AddressID, I need to identify the MAX(PersonID) so that the co
mbination of AddressID
>and MAX(PersondID) is not affected.
>For all other combinations of any single AddressID and PersonIDs, I need to
make the NextMailDate
>NULL where the existing NextMailDate is equal to or greater than the system date.[/
color]
(snip)
Hi larzeb,
Not sure if I understand the requirements correctly, but maybe this is
what you want:
UPDATE Mailings
SET NextMailDate IS NULL
WHERE NextMailDate > CURRENT_TIMESTAMP
AND PersonID <> (SELECT MAX(PersonID)
FROM Mailings AS m2
WHERE m2.AddressID = Mailings.AddressID)
Try on test data first. Run inside a transaction, and check the results
before executing COMMIT (or ROLLBACK if the results are wrong).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Looking at the problem again, Hugo's response would be the solution you want
.
That said this is a pretty bizarre situation asking for the Max personId.
Thomas
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:erNavS9XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Firstly, don't depend on Identity columns as a guarantee of entry sequence
.
> You should instead add a column called DateTime column that marks when the
row
> was added.
>
> This logic does not appear to match your sample results. Given your
> requirements, I would expect the following results
> AddressId PersonId NextMailDate
> 123 4 2005-03-10
> 123 4 Null
> 123 4 Null
> 123 12 Null
> If this is not the case, then why should the last entry for a given Addres
sId
> and PersonId but a date greater than the system date not get reset?
>
> Thomas
>
> "larzeb" <larzeb@.community.nospam> wrote in message
> news:627491d56eo3jrbu313505o318e1kn3bh3@.
4ax.com...
>|||On Mon, 23 May 2005 13:33:08 -0700, Thomas Coleman wrote:

>Looking at the problem again, Hugo's response would be the solution you wan
t.
>That said this is a pretty bizarre situation asking for the Max personId.
Hi Thomas,
My thoughts exactly - that's why I thought that I might have misread
something :)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)