Re: Can I replace RPG with SQL?

**************
On Thu, Sep 16, 2010 at 11:39 AM, Charles wrote:

how about:
update ClientTable A
set ClientVersion = (select max(CilentVersion) + 1
from ClientTable B
where a.clientID = b.clientID)
where clientID in (select clientID from NewClientTable);

insert into ClientTable (ClientID, ClientName, ClientVersion)
(select ClientID, ClientName, 0
from NewClientTable);
****************

Charles, (or anyone else). I'm looking at Luis' idea for putting this in a stored procedure. However, I have a small problem with columns on the insert.

What the procedure is supposed to do :

1. Take the client record, and update its version number from 0 to n+1 where n is the highest version number in the table for that client.
2. Create a new client record, with the same information as the original, ie with version number 0, but with the name from the NewClientTable.

Now what if other columns exist in a record, say dateofbirth? This information is not in NewClientTable. It is in the record that has just been updated to version number n+1. How can I populate the new record with this dateofbirth?

Thanks.