| adding duplicate records in plsql procedure [message #370061] |
Fri, 08 December 2000 03:11  |
k.j kombrink
Messages: 2
Registered: September 2000
|
Junior Member |
|
|
Hi maybe someone can help me with this one!
I have a table (no pk) with the following records:
id productno quantity price total
1 1000 1 10 10
1 1000 2 10 20
1 1000 1 10 10
1 1001 1 20 20
1 1002 1 15 15
1 1002 3 15 45
1 1003 2 25 50
1 1003 1 25 25
Now I want to create a prim key on id and productno.
so that i get a table like this
1 1000 4 10 40
etc
So i have to add up all the duplicate records and then
delete them from this table leaving 1 record for each id.
I solved this as follows:
1. I created a plsql table (TYPE table_of_varchar2 IS
TABLE OF my table_name%ROWTYPE INDEX BY
BINARY_INTEGER;) and declared an instance of this
table: plsql_tab table_of_varchar2;
2. I created filled this table with the totalled
records using a cursor (select collumns from
my_table HAVING COUNT (*) > 1 group by collumns)
and filling my plsql table using:
LOOP
i := i + 1; -- declared as integer
FETCH cursor INTO psql_tab (i);
EXIT WHEN c_cursor%NOTFOUND;
END LOOP;
3. I then inserted all my duplicate rows from the orignal table into an exceptions table and subsequently deleted all the duplicate rows from my original table using the following select statement:
insert into dup_tab
select a.rowid, a.* from dup_tab a
where a.rowid > (select min(b.rowid)
from my_table b
where a.col1 = b.col1 and
a.col2 = b.col2)
or a.rowid < (select min(b.rowid)
from my_table b
where a.col1 = b.col1 and
a.col2 = b.col2);
I then deleted the duplicates using this rowid (delete from my_table where rowid in(select rowid from exceptions_table);
and of course i cleaned up my exceptions table.
4.Lastly i inserted back into my_table the values from the plsql table using a for loop:
FOR i IN plsql_tab.FIRST .. plsql_tab.LAST
insert into my_table (columnnames)
values (plsql_tab (i).id
etc);
My question now is... isn't there an easier way to do this using just one cursor and a for loop?
It would be nice if someone could help me out here as i feel that i wrote a pretty large procedure for what seemed a simple problem at first.
|
|
|
|
| Re: adding duplicate records in plsql procedure [message #370062 is a reply to message #370061] |
Fri, 08 December 2000 05:13  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
You don't even need to use Pl/Sql.
1) Update all the rows in the table to hold the sum of the quantity and total for that product no
Update Table t1
set (qty,total) = (select sum(qty),sum(total)
from table t2
where t1.product_no = t2.product_no)
2) Delete all but one row for each product_no
Delete table t1
where exists (select 'x'
from table t2
where t1.productno = t2.productno
and t1.rowid> t2.rowid)
Hope this helps.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
