Hi everyone,
I have a task to sync two tables in Oracle. There are two sets of tables. One is the set used by the application. The other set of tables are identical but each has an action field that is used do show if the row should be an add, update, or delete. An interface tool is populating these. I must take these interface tables and update the originals. I hope that makes sense.
Anyway, in FoxPro, I could just loop through each row, run "scatter memvar", locate/seek for the matching row in the original table, and run "gather memvar".
Does anyone know of an approach in Oracle like that or perhaps have some advice on doing this?
Try this. https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/tdddg_procedures.htm
There is no scatter memver in oracle.
On Tue, Sep 13, 2016 at 6:43 PM, Matt Wiedeman Matt.Wiedeman@nahealth.com wrote:
Hi everyone,
I have a task to sync two tables in Oracle. There are two sets of tables. One is the set used by the application. The other set of tables are identical but each has an action field that is used do show if the row should be an add, update, or delete. An interface tool is populating these. I must take these interface tables and update the originals. I hope that makes sense.
Anyway, in FoxPro, I could just loop through each row, run "scatter memvar", locate/seek for the matching row in the original table, and run "gather memvar".
Does anyone know of an approach in Oracle like that or perhaps have some advice on doing this?
[excessive quoting removed by server]
Hi Can you use a SQL update ? Regards Koen
Op woensdag 14 september 2016 heeft Stephen Russell srussell705@gmail.com het volgende geschreven:
Try this. https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/ tdddg_procedures.htm
There is no scatter memver in oracle.
On Tue, Sep 13, 2016 at 6:43 PM, Matt Wiedeman <Matt.Wiedeman@nahealth.com javascript:;> wrote:
Hi everyone,
I have a task to sync two tables in Oracle. There are two sets of tables. One is the set used by the application. The other set of tables are identical but each has an action field that is used do show if the row should be an add, update, or delete. An interface tool is populating
these.
I must take these interface tables and update the originals. I hope that makes sense.
Anyway, in FoxPro, I could just loop through each row, run "scatter memvar", locate/seek for the matching row in the original table, and run "gather memvar".
Does anyone know of an approach in Oracle like that or perhaps have some advice on doing this?
[excessive quoting removed by server]
I could write a massive update statement but there are a lot of fields. I may end up doing it that way.
I just found the %ROWTYPE attribute...it seems to work like a "scatter memvar" but there is no "gather memvar" equivalent that I can find.
-----Original Message----- From: Koen Piller [mailto:koen.piller@gmail.com] Sent: Wednesday, September 14, 2016 1:24 AM To: ProFox Email List Subject: Re: Oracle question
Hi Can you use a SQL update ? Regards Koen
Op woensdag 14 september 2016 heeft Stephen Russell srussell705@gmail.com het volgende geschreven:
Try this. https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/ tdddg_procedures.htm
There is no scatter memver in oracle.
On Tue, Sep 13, 2016 at 6:43 PM, Matt Wiedeman <Matt.Wiedeman@nahealth.com javascript:;> wrote:
Hi everyone,
I have a task to sync two tables in Oracle. There are two sets of tables. One is the set used by the application. The other set of tables are identical but each has an action field that is used do show if the row should be an add, update, or delete. An interface tool is populating
these.
I must take these interface tables and update the originals. I hope that makes sense.
Anyway, in FoxPro, I could just loop through each row, run "scatter memvar", locate/seek for the matching row in the original table, and run "gather memvar".
Does anyone know of an approach in Oracle like that or perhaps have some advice on doing this?
[excessive quoting removed by server]
If you use Oracle ODBC to connect, a VFP updateable view on the table you wish to update, and a second read-only view from the source table, you can scatter and gather (and then TABLEUPDATE() ) between the two.
On Wed, Sep 14, 2016 at 9:08 AM, Matt Wiedeman Matt.Wiedeman@nahealth.com wrote:
I could write a massive update statement but there are a lot of fields. I may end up doing it that way.
I just found the %ROWTYPE attribute...it seems to work like a "scatter memvar" but there is no "gather memvar" equivalent that I can find.
-----Original Message----- From: Koen Piller [mailto:koen.piller@gmail.com] Sent: Wednesday, September 14, 2016 1:24 AM To: ProFox Email List Subject: Re: Oracle question
Hi Can you use a SQL update ? Regards Koen
Op woensdag 14 september 2016 heeft Stephen Russell srussell705@gmail.com het volgende geschreven:
Try this. https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/ tdddg_procedures.htm
There is no scatter memver in oracle.
On Tue, Sep 13, 2016 at 6:43 PM, Matt Wiedeman <Matt.Wiedeman@nahealth.com javascript:;> wrote:
Hi everyone,
I have a task to sync two tables in Oracle. There are two sets of tables. One is the set used by the application. The other set of tables are identical but each has an action field that is used do show if the row should be an add, update, or delete. An interface tool is populating
these.
I must take these interface tables and update the originals. I hope that makes sense.
Anyway, in FoxPro, I could just loop through each row, run "scatter memvar", locate/seek for the matching row in the original table, and run "gather memvar".
Does anyone know of an approach in Oracle like that or perhaps have some advice on doing this?
[excessive quoting removed by server]
I live in the area of Waffle House restaurants and every time I read Scatter I think of ordering hashbrows. Below is the list of how you can order them and they all start with Scattered....
"scattered" (spread on the grill), "smothered" (with onions), "covered" (with cheese), "chunked" (with diced ham), "diced" (with diced tomatoes), "peppered" (with jalapeño peppers), "capped" (with mushrooms), "topped" (with chili) and "all the way" (with all available toppings). Recently, the option of "country" was added for hashbrowns with sausage gravy https://en.wikipedia.org/wiki/Sausage_gravy on them.
Now I am hungry again.
On Wed, Sep 14, 2016 at 8:27 AM, Ted Roche tedroche@gmail.com wrote:
If you use Oracle ODBC to connect, a VFP updateable view on the table you wish to update, and a second read-only view from the source table, you can scatter and gather (and then TABLEUPDATE() ) between the two.
On Wed, Sep 14, 2016 at 9:08 AM, Matt Wiedeman Matt.Wiedeman@nahealth.com wrote:
I could write a massive update statement but there are a lot of fields.
I may end up doing it that way.
I just found the %ROWTYPE attribute...it seems to work like a "scatter
memvar" but there is no "gather memvar" equivalent that I can find.
-----Original Message----- From: Koen Piller [mailto:koen.piller@gmail.com] Sent: Wednesday, September 14, 2016 1:24 AM To: ProFox Email List Subject: Re: Oracle question
Hi Can you use a SQL update ? Regards Koen
Op woensdag 14 september 2016 heeft Stephen Russell <
srussell705@gmail.com> het volgende geschreven:
Try this. https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/ tdddg_procedures.htm
There is no scatter memver in oracle.
On Tue, Sep 13, 2016 at 6:43 PM, Matt Wiedeman <Matt.Wiedeman@nahealth.com javascript:;> wrote:
Hi everyone,
I have a task to sync two tables in Oracle. There are two sets of
tables.
One is the set used by the application. The other set of tables are identical but each has an action field that is used do show if the row should be an add, update, or delete. An interface tool is populating
these.
I must take these interface tables and update the originals. I hope that makes sense.
Anyway, in FoxPro, I could just loop through each row, run "scatter memvar", locate/seek for the matching row in the original table, and run "gather memvar".
Does anyone know of an approach in Oracle like that or perhaps have some advice on doing this?
[excessive quoting removed by server]
Matt how many fields in the row need to be updated?
To me your process is get all rows that need to be inserted into other system. Then insert that data into other system without the flag column right? /* Anyway, in FoxPro, I could just loop through each row, run "scatter memvar", locate/seek for the matching row in the original table, and run "gather memvar". */
One statement to do it is below for Oracle
INSERT INTO tbl_temp2 (fld_id, field list here....)SELECT tbl_temp1.fld_order_id, field list here ....FROM tbl_temp1 WHERE tbl_temp1.columnYouFindTheChangeHere;
HTH
On Wed, Sep 14, 2016 at 8:08 AM, Matt Wiedeman Matt.Wiedeman@nahealth.com wrote:
I could write a massive update statement but there are a lot of fields. I may end up doing it that way.
I just found the %ROWTYPE attribute...it seems to work like a "scatter memvar" but there is no "gather memvar" equivalent that I can find.
-----Original Message----- From: Koen Piller [mailto:koen.piller@gmail.com] Sent: Wednesday, September 14, 2016 1:24 AM To: ProFox Email List Subject: Re: Oracle question
Hi Can you use a SQL update ? Regards Koen
Op woensdag 14 september 2016 heeft Stephen Russell srussell705@gmail.com het volgende geschreven:
Try this. https://docs.oracle.com/cd/B28359_01/appdev.111/b28843/ tdddg_procedures.htm
There is no scatter memver in oracle.
On Tue, Sep 13, 2016 at 6:43 PM, Matt Wiedeman <Matt.Wiedeman@nahealth.com javascript:;> wrote:
Hi everyone,
I have a task to sync two tables in Oracle. There are two sets of
tables.
One is the set used by the application. The other set of tables are identical but each has an action field that is used do show if the row should be an add, update, or delete. An interface tool is populating
these.
I must take these interface tables and update the originals. I hope that makes sense.
Anyway, in FoxPro, I could just loop through each row, run "scatter memvar", locate/seek for the matching row in the original table, and run "gather memvar".
Does anyone know of an approach in Oracle like that or perhaps have some advice on doing this?
[excessive quoting removed by server]
Correlated update?
http://stackoverflow.com/questions/7030699/oracle-sql-update-a-table-with-da...
... and remember to get the user to define the insert/update/delete priorities: should a delete/insert pair be treated as an update? or should an insert/delete pair be treated as no-op? an insert/update pair should normally be allowed, but will delete/update (or any non-existing record) throw an error? Will the whole job be aborted on errors?
These sort of questions go way back to batch-processing and tape drives! - which makes me wonder just why anyone would ever want to do this on an RDB?
On Thu, Sep 15, 2016 at 10:11 AM, AndyHC andy@hawthorncottage.com wrote:
These sort of questions go way back to batch-processing and tape drives! - which makes me wonder just why anyone would ever want to do this on an RDB?
Decades ago, a wise man told me, "there are only twenty-three problems in computer science. We keep solving them over and over."
-
Alan Bourke -
AndyHC -
Koen Piller -
Matt Wiedeman -
Stephen Russell -
Ted Roche