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.

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.

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

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.

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?