Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
How do I write an "Update if different" SQL. |
Mon, Jan 23 2017 6:18 AM | Permanent Link |
Adam Brett Orixa Systems | 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 -- |
Mon, Jan 23 2017 8:07 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Jan 23 2017 11:11 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Tue, Jan 24 2017 4:03 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Jan 24 2017 3:20 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Jan 25 2017 4:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Jan 27 2017 12:31 PM | Permanent Link |
Adam Brett Orixa Systems | 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 |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |