• DELETE WHERE CURRENT

    From Adam Kamal@21:1/5 to All on Tue Dec 14 10:05:28 2021
    Trying to find the syntax DELETE WHERE CURRENT, did not find any info. Ultimately, I am READING 2 tables via inner join,
    and then update the company # on the record. Since company# is a key, so will have to delete and reinsert with new company number.

    I have few question

    1- How would you declare the cursor? is it different than READ only cursor? I am getting error 8223 when perform the delete. Currently it's as below: does this looks correct?
    EXEC SQL
    DECLARE GET_OPEN_ITEM CURSOR FOR
    SELECT A.COLUMN1
    , B.COLUMN1
    FROM =TABLE1 A, TABLE2 B
    INNER JOIN TABLE1 ON TABLE2
    WHERE A.COLUMN1 = B.COLUMN2
    BROWSE ACCESS
    END-EXEC

    2- When I perform a record DELETE, I will need to write 2 different DELETE statements, once for each table, correct?

    3- I tried to do a DELETE and INSERT without using DELETE WHERE CURRENT, just regular delete, it worked. But when I fetch I still get the same record I just delete and inserted with new company #. The thing is when the process try to delete again, I
    get error 100 , so I do not understand how it got fetched the second time. Mu guess its has to do with buffering .

    Any idea or thoughts will be highly appreciated.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Randall@21:1/5 to abol...@gmail.com on Tue Dec 14 11:15:26 2021
    On Tuesday, December 14, 2021 at 1:05:30 p.m. UTC-5, abol...@gmail.com wrote:
    Trying to find the syntax DELETE WHERE CURRENT, did not find any info. Ultimately, I am READING 2 tables via inner join,
    and then update the company # on the record. Since company# is a key, so will have to delete and reinsert with new company number.

    I have few question

    1- How would you declare the cursor? is it different than READ only cursor? I am getting error 8223 when perform the delete. Currently it's as below: does this looks correct?
    EXEC SQL
    DECLARE GET_OPEN_ITEM CURSOR FOR
    SELECT A.COLUMN1
    , B.COLUMN1
    FROM =TABLE1 A, TABLE2 B
    INNER JOIN TABLE1 ON TABLE2
    WHERE A.COLUMN1 = B.COLUMN2
    BROWSE ACCESS
    END-EXEC

    2- When I perform a record DELETE, I will need to write 2 different DELETE statements, once for each table, correct?

    3- I tried to do a DELETE and INSERT without using DELETE WHERE CURRENT, just regular delete, it worked. But when I fetch I still get the same record I just delete and inserted with new company #. The thing is when the process try to delete again, I
    get error 100 , so I do not understand how it got fetched the second time. Mu guess its has to do with buffering .

    Any idea or thoughts will be highly appreciated.

    It appears that your issue is that you are using BROWSE ACCESS. If you are doing transaction processing, use either default or EXCLUSIVE access. Then you should not get the record fetched after deleted.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Bill Honaker@21:1/5 to Adam Kamal on Tue Dec 14 15:18:27 2021
    On Tue, 14 Dec 2021 10:05:28 -0800 (PST), Adam Kamal <abolkini@gmail.com> wrote:

    Trying to find the syntax DELETE WHERE CURRENT, did not find any info. >Ultimately, I am READING 2 tables via inner join,
    and then update the company # on the record. Since company# is a key, so will have to delete and reinsert with new company number.

    I have few question

    1- How would you declare the cursor? is it different than READ only cursor? I am getting error 8223 when perform the delete. Currently it's as below: does this looks correct?
    EXEC SQL
    DECLARE GET_OPEN_ITEM CURSOR FOR
    SELECT A.COLUMN1
    , B.COLUMN1
    FROM =TABLE1 A, TABLE2 B
    INNER JOIN TABLE1 ON TABLE2
    WHERE A.COLUMN1 = B.COLUMN2
    BROWSE ACCESS
    END-EXEC

    2- When I perform a record DELETE, I will need to write 2 different DELETE statements, once for each table, correct?

    3- I tried to do a DELETE and INSERT without using DELETE WHERE CURRENT, just regular delete, it worked. But when I fetch I still get the same record I just delete and inserted with new company #. The thing is when the process try to delete again, I
    get error 100 , so I do not understand how it got fetched the second time. Mu guess its has to do with buffering .

    Any idea or thoughts will be highly appreciated.

    According to the DECLASRE CURSOR documentatation in the SQL/MP Reference manual (page 230), if a SELECT in a DECLARE CURSOR udpates or deletes rows,
    the FROM clause can only include one table or protection view, and connot include a JOIN operator.

    You are probably better off executing the statement above and create a list of company numbers which you want to process.
    Then loop through the list and delete/insert. Best to begin/commit your transactions with each delete/insert.

    It would seem fromr your example you're working with a COBOL program, depending on how many companies you expect to update,
    you may need to write to an Enscribe file (or even a text file). Also, you probably need to select ALL columns from the rows if you plan
    to insert, I suspect 'A' and 'B' may not be enough.

    If your program were in C you could allocate memory to keep the list in.

    Bill

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Adam Kamal@21:1/5 to Randall on Tue Dec 14 16:13:51 2021
    On Tuesday, December 14, 2021 at 11:15:27 AM UTC-8, Randall wrote:
    On Tuesday, December 14, 2021 at 1:05:30 p.m. UTC-5, abol...@gmail.com wrote:
    Trying to find the syntax DELETE WHERE CURRENT, did not find any info. Ultimately, I am READING 2 tables via inner join,
    and then update the company # on the record. Since company# is a key, so will have to delete and reinsert with new company number.

    I have few question

    1- How would you declare the cursor? is it different than READ only cursor? I am getting error 8223 when perform the delete. Currently it's as below: does this looks correct?
    EXEC SQL
    DECLARE GET_OPEN_ITEM CURSOR FOR
    SELECT A.COLUMN1
    , B.COLUMN1
    FROM =TABLE1 A, TABLE2 B
    INNER JOIN TABLE1 ON TABLE2
    WHERE A.COLUMN1 = B.COLUMN2
    BROWSE ACCESS
    END-EXEC

    2- When I perform a record DELETE, I will need to write 2 different DELETE statements, once for each table, correct?

    3- I tried to do a DELETE and INSERT without using DELETE WHERE CURRENT, just regular delete, it worked. But when I fetch I still get the same record I just delete and inserted with new company #. The thing is when the process try to delete again, I
    get error 100 , so I do not understand how it got fetched the second time. Mu guess its has to do with buffering .

    Any idea or thoughts will be highly appreciated.
    It appears that your issue is that you are using BROWSE ACCESS. If you are doing transaction processing, use either default or EXCLUSIVE access. Then you should not get the record fetched after deleted.

    Hello Randall,
    Changed the cursor BROWSE ACCESS to REPRATABLE ACCESS, but it does not seem to work. Program continues to pickup already processed record. I guess the buffer where the data is stored on OPEN cursor is changing when I delete/insert the record.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Randall@21:1/5 to abol...@gmail.com on Wed Dec 15 10:47:48 2021
    On Tuesday, December 14, 2021 at 7:13:52 p.m. UTC-5, abol...@gmail.com wrote:
    On Tuesday, December 14, 2021 at 11:15:27 AM UTC-8, Randall wrote:
    On Tuesday, December 14, 2021 at 1:05:30 p.m. UTC-5, abol...@gmail.com wrote:
    Trying to find the syntax DELETE WHERE CURRENT, did not find any info. Ultimately, I am READING 2 tables via inner join,
    and then update the company # on the record. Since company# is a key, so will have to delete and reinsert with new company number.

    I have few question

    1- How would you declare the cursor? is it different than READ only cursor? I am getting error 8223 when perform the delete. Currently it's as below: does this looks correct?
    EXEC SQL
    DECLARE GET_OPEN_ITEM CURSOR FOR
    SELECT A.COLUMN1
    , B.COLUMN1
    FROM =TABLE1 A, TABLE2 B
    INNER JOIN TABLE1 ON TABLE2
    WHERE A.COLUMN1 = B.COLUMN2
    BROWSE ACCESS
    END-EXEC

    2- When I perform a record DELETE, I will need to write 2 different DELETE statements, once for each table, correct?

    3- I tried to do a DELETE and INSERT without using DELETE WHERE CURRENT, just regular delete, it worked. But when I fetch I still get the same record I just delete and inserted with new company #. The thing is when the process try to delete again,
    I get error 100 , so I do not understand how it got fetched the second time. Mu guess its has to do with buffering .

    Any idea or thoughts will be highly appreciated.
    It appears that your issue is that you are using BROWSE ACCESS. If you are doing transaction processing, use either default or EXCLUSIVE access. Then you should not get the record fetched after deleted.
    Hello Randall,
    Changed the cursor BROWSE ACCESS to REPRATABLE ACCESS, but it does not seem to work. Program continues to pickup already processed record. I guess the buffer where the data is stored on OPEN cursor is changing when I delete/insert the record.

    Do you have BEGIN WORK, COMMIT WORK, BEGIN WORK in between?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)