Not an error but help required
Posted: Tue 20 Sep 2016 20:46
Hi
I'm not sure if I should be posting this here but I'll ask anyway
I have a remote database on another continent and I have to update all the records in one of the tables regularly using Delphi Seattle, UniDac and Securebridge.
The database (not of my design ) has approx. 21,000 records and one of the fields is an integer field which contains a value used as the sort order which is dependant on three other fields. I have to reorder the table reqularly for reasons beyond my control and to do this i fetch the records with
SELECT id,ordering from mytable order by surname,forename,dob.
I then have to have to go through the whole table updating ordering with the position the record is in the query.
I've worked with SQL databases for many years but they have always been small and fairly local or I was able to have a program on the server to do this for me. This is the first time I've needed to do an update of this magnitude on a server which is linux and on a hosted shared server.
So I would like suggestions on the fastest and safest way of doing this using the above mentioned components.
I would guess that the last thing thing I need to be doing is editing each record individually, so I would further guess that I make up a list of 'UPDATE mytable SET ordering=x WHERE id=xx;' but what is the best way to get them back to the server or is there some other way of doing this.
Thank You
Niall
I'm not sure if I should be posting this here but I'll ask anyway
I have a remote database on another continent and I have to update all the records in one of the tables regularly using Delphi Seattle, UniDac and Securebridge.
The database (not of my design ) has approx. 21,000 records and one of the fields is an integer field which contains a value used as the sort order which is dependant on three other fields. I have to reorder the table reqularly for reasons beyond my control and to do this i fetch the records with
SELECT id,ordering from mytable order by surname,forename,dob.
I then have to have to go through the whole table updating ordering with the position the record is in the query.
I've worked with SQL databases for many years but they have always been small and fairly local or I was able to have a program on the server to do this for me. This is the first time I've needed to do an update of this magnitude on a server which is linux and on a hosted shared server.
So I would like suggestions on the fastest and safest way of doing this using the above mentioned components.
I would guess that the last thing thing I need to be doing is editing each record individually, so I would further guess that I make up a list of 'UPDATE mytable SET ordering=x WHERE id=xx;' but what is the best way to get them back to the server or is there some other way of doing this.
Thank You
Niall