I have multiple versions of a database. Usually these would be synchronized using EDB's "PUBLISH" features, but due to errors made in installation this cannot be done.

I am trying to write SQL to synchronize some fairly simple data:

FirstName, LastName, DateOfBirth

If the data is different in database "2" this different version should be updated to database "1"

As the database is very large, we only want to check a few of the "People" to speed things up

I have written a statement, however it sets most of the values in database "1" to NULL (!!!!) rather than only updating those that have been changed.

I am pulling my hair out trying to figure out a way that will work

--

UPDATE Database1.People P
SET
 (P.FirstName, P.LastName, P.DateOfBirth) =

SELECT
 FirstName, LastName, DateOfBirth
FROM Database2.People RUP
WHERE RUP.ID = P.ID                      --join the tables being updated
AND P.ID IN                                       --only update for selected "Depot"
( SELECT ID FROM Database1.People P1
 WHERE P1.SocietiesID IN
 (
   SELECT
     ID
   FROM Database1.Societies
   WHERE DepotsID IN (42)
 )
)
AND NOT (RUP.ID, RUP.FullName, RUP.DateOfBirth)
 = (P.ID, P.FullName, P.DateOfBirth)  --only update if the fields are different

--

Adam


I find myself struggling with that statement.

Sorry for asking but have you tested each of the SELECT statments individually to see if they work?

Looking at it a bit more I would start with

AND NOT (RUP.ID, RUP.FullName, RUP.DateOfBirth)
 = (P.ID, P.FullName, P.DateOfBirth)  --only update if the fields are different

If you're using ID as the primary key for the table this (I think) guarantees no match and hence the nulls.

Roy Lambert

Thanks Roy,

You are indefatigable, and I definitely owe you at least a few beers.

The thing I find strange is that when I format the statement as a "SELECT" (i.e. removing the UPDATE portion) it functions perfectly, returning the few hundred rows that are specified, and taking perhaps 30 - 50 seconds to run.

When run as an UPDATE, firstly it is beyond slow ... it takes hours to run ... and secondly it seems to null any rows it does not find, although I am specifically asking it only to to update

WHERE P.ID IN (...)

i.e., P.ID's that are NOT returned by the IN clause still seem to be being updated. This seems to be against my understanding of how UPDATE's should work.

I think your idea about removing the ID reference from later in the WHERE clause is a good one though. I will try that!

Thanks again.

Adam


>You are indefatigable,

That statement is approxiamtely 100% totally incorrect!

>
>The thing I find strange is that when I format the statement as a "SELECT" (i.e. removing the UPDATE portion) it functions perfectly, returning the few hundred rows that are specified, and taking perhaps 30 - 50 seconds to run.
>
>When run as an UPDATE, firstly it is beyond slow ... it takes hours to run

Well, I suspect that asking it to update c500k rows will take a bit of time, as well as working out for each row that there isn't a corresponding row to extract data from. Also did you do more than change UPDATE to SELECT, if so its the changes that have made the difference.

The other thing you might want to look at is the execution plan for individual elements of the query - I suspect that at least one is resorting to a row scan in the assembled code.

>... and secondly it seems to null any rows it does not find, although I am specifically asking it only to to update

So something has to be returning nulls

This is where I generally fall back on experimentation - which is a bit difficult with someone else's database and tables. This is what I'd do to try and figure things out:

1. pick one table as the trial
2. delete most of the rows (you need to test on the 500k row table at somepoint but not for troubleshooting)
3. clone that table and keep that copy so you can get back to the same starting point
4. clone that table and change 1 (ONE) row in the table
5. now start messing about

What I've found on occasions is that the test case works perfectly which points to data as being the problem. In extremis I've had to resort to Delphi code to essentially duplicate the SQL and hand crank things through to isolate the cause.

If you want to email me a reverse engineered table including code to populate it with a few hundred rows I'm willing to have a look at things.

Roy Lambert

Adam,

<< I have written a statement, however it sets most of the values in database "1" to NULL (!!!!) rather than only updating those that have been changed. >>

As far as I can tell, you don't have any WHERE clause on the outer table being updated, hence every row will get updated with whatever gets returned from the source expression on the right side of the SET clause.  To prevent rows from even being *considered* for update, you'll need to add a WHERE clause to the UPDATE itself.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

<<you don't have any WHERE clause on the outer table being updated>>


I missed that totally - got confused by the nesting

Roy Lambert

Tim

D'Oh.

Oh the pain.

My stupid error. I don't use UPDATEs very often. But this has forced me to really understand how they work.

Thanks.

Adam