I have 2 tables that have the same fields and the same data. However when I counted the records, I found that one table contain more records than the other. Now I need to delete the extra records and make the table identical. How can I do this using cursor?

Please advice it's urgent

First of all, are you sure you only have extra records in the second (or first, whatever) table ?

Indeed, if table A contains the followings rows
1
2
3

and table B contains
1
2
4
5

then deleting all "extra" rows in table B will end you up with 2 rows in table B, which is not exactly what you want...

Don't bother using a cursor when you can do things directly :

DELETE FROM B WHERE b.pk NOT IN (SELECT a.pk FROM A)

where pk stands for primary key (or some unique key).

Anyway, to make two tables identical, i would just truncate the "bad" one, and re-insert all rows from the "good" one.

Thank You for your reply ..

The problem that it's not that easy. One table is in Oracle Platform and the other is in as400 (IBM platform) but I took care of that. The other problem is that the oracle table contain around 87000 records and as400 table has 84000 although we are running a batch file to copy the same records to both tables. The normal way of doing this will take at least 1 hour to run the query. So I tought cursors will make it faster.

What do you think?

I'm still trying to understand. Can you confirm following assumptions ?

1. Your "source" table is the one on the AS400, and it contains all data you need.

2. Your "duplicate" table is the one on Oracle, and it contains too many rows.

3. Now, you would like to "restore" the table on Oracle using data from your table on the AS400.

CVM.

Yes this is exactly what I need and note that there are 4 PK for the tables. What I found out that sometimes when the user delete a record from the as400 table it's not deleted in oracle. that's why we have more records in oracle.

the system we have is basically like this

as400 master table : store original data

as400 intermediate file : takes the data from the master table and store them

oracle intermediate file: take the information from the as400 intermediate file and store them

oracle master table: takes the information from oracle intermediate table.

so at the end the as400 master table and the oracle master table must be identical however they are not.

Now that I've got your answers, let's get back to your original problem.

1. Of course you can do it using cursors (read from as400, see if it exists on Oracle, ...). This is quite straightforward to implement, but it will not be faster than copying the data directly (see point 2). Moreover, you need to write code (and we don't like that, do we...)

2. My current project also involves an AS400 (source) and an Oracle database (destination). I cannot imagine that those 87000 rows are giving you a hard time. I don't know the size of a row, nor do I know about the number of indexes, etc. that are linked to the destination table, but I can simply not imagine that it takes about 1 hour. Did you try using the (Sql*Plus) command COPY FROM ?

I know that there are some simple ways of doing this, but it's not dicision to make. Moreover, these table are so active and users are accessing it every seconds so there should be no chance for any mistake or loss or data. What my boss suggested is to run cursors for both tables and store as oracle record in local variables, search for it in as400, if not exists then delete it. But It's not working.
The tables doesn't use indexes.

What exactly do you mean by "The tables doesn't use indexes" ?

Anyway, I'm a getting out of ideas here. Knowing a little bit about the AS400, and considering the fact that you don't want to have mistakes/loss or data during your patch, my guess is you need to run your patch using a very high commitment control level (on the AS400) like REPEATABLE READ, which gets your users locked anyway.