Hi,

I need to update a table using SQL (if possible), but have hit a snag.

The table I need to update is a Fee table. (Contains different fee codes,
and amounts).

I need to update the Fee table, so the date paid is equal to a certain
formula - such as x days from invoice date (the easy part).

The problem I have, is that in the Fee table, there are some records that
have two amounts (identical) for the same fee. (So, essentially, looking at
the table, it appears as though you have 2 of the same records for each
invoice / primary key).

In this instance - when the same fee exists more than once for a particular
invoice, the first record will need to be updated to a particular date, as
per above, but the second record for the same fee, needs to have a different
date updated.

Is there a way that I can do this using SQL?

Thanks & Regards

Adam.

Adam,

<< In this instance - when the same fee exists more than once for a
particular invoice, the first record will need to be updated to a particular
date, as per above, but the second record for the same fee, needs to have a
different date updated.

Is there a way that I can do this using SQL? >>

Probably not, but just to be sure, could you post the structure of the Fees
table ?  I'm curious as to what all of the fields are and what indexes are
defined.  I'm having a hard time picturing why there are two duplicate
records.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

> Probably not, but just to be sure, could you post the structure of the
> Fees table ?  I'm curious as to what all of the fields are and what
> indexes are defined.  I'm having a hard time picturing why there are two
> duplicate records.

No problems. Here's the structure:

Invoice Table: (Only relevant fields)
~~~~~~~~~~
ID : GUID {Primary Index}
Customer : String
Date : TDate


Fee Table:
~~~~~~~
ID : GUID {Primary Index}
ParentID: GUID  (Links to ID on Invoice Table - secondary index)
Amount: Currency
DatePaid : TDate


As a thought - if I was to update both identical records in the fee table
with the same date/amount, and then run a second SQL script saying something
like (SQL may not be exact, but to give you a picture):

update fee table
set datepaid = x
where parentid in (select count(ParentID), ParentID, Amount, DatePaid
                           from FeeTable
                           group by ParentID, Amount, DatePaid
                           having count(ParentID) > 1)

Would that update both records - or would it change the first record, and
then re-run the "where in"  SQL and see that only one record is left, and
not update it?

Thanks & Regards

Adam.

Adam,

<< As a thought - if I was to update both identical records in the fee table
with the same date/amount, and then run a second SQL script saying something
like (SQL may not be exact, but to give you a picture):

update fee table
set datepaid = x
where parentid in (select count(ParentID), ParentID, Amount, DatePaid
                           from FeeTable
                           group by ParentID, Amount, DatePaid
                           having count(ParentID) > 1)

Would that update both records - or would it change the first record, and
then re-run the "where in"  SQL and see that only one record is left, and
not update it? >>

It would change both.  Frankly, the only way you can do something like this
is either navigationally or by using a temporary intermediate table where
you "number" the duplicates using RUNSUM() or something similar in order to
distinguish them from each other.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:5D699574-F938-4ED4-9676-54208545A635@news.elevatesoft.com...
>
> It would change both.  Frankly, the only way you can do something like
> this is either navigationally or by using a temporary intermediate table
> where you "number" the duplicates using RUNSUM() or something similar in
> order to distinguish them from each other.
>


He has a GUI that makes the records unique. Something like this might do it

update table t1
set t1.field = if(t2.field = 1 then 2 else 1)
left outer join table t2 on (t1.Foreign = t2.foreign and t1.gui <> t2.GUI)

if he does not have a duplicate record t2 field will be null, and he will
set field to 1. If he has a duplicate that has already been updated,
t2.field is 1 so we set t1.field to 2. Of course, we're assuming a max of 1
duplicate.

I think there is another test needed here, but I don't have the time right
now to work on this. But you get the idea.

Robert

Robert,

<< He has a GUI that makes the records unique. Something like this might do
it >>

I understand, but what I meant was not simply unique, but rather a way of
telling one from the other in terms of whether it has been updated or not.
What you're doing may do the trick.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim and Robert,

Thanks heaps for your help. I'll give that a try, and see what I can come up
with!

Best Regards

Adam.

Adam,

<< Thanks heaps for your help. I'll give that a try, and see what I can come
up with! >>

Don't thank me - I didn't do anything.

--
Tim Young
Elevate Software
www.elevatesoft.com