Home » SQL & PL/SQL » SQL & PL/SQL » update using group by
update using group by [message #188732] Mon, 21 August 2006 06:10 Go to next message
hershs
Messages: 5
Registered: August 2006
Junior Member
Hi ,
I have two tables:
data (dataid as number, refcount as number,...)
and
versions (id as number,dataid as number,...)

I need update 'data' table 'refcount' comlumn with number of lines with same 'dataid' from versions table.

One of the ways is execute the output of following select:

select 'update data set refcouter = '||count(v.dataid )||' where dataid = '||v.dataid||';' 
from versions v
group by v.dataid

The problem is that I have very more rows in the DB and the script runs long time.

Do you know another way to do it?

Thanks
Re: update using group by [message #188735 is a reply to message #188732] Mon, 21 August 2006 06:29 Go to previous messageGo to next message
rameshuddaraju
Messages: 69
Registered: June 2005
Location: India
Member

hi,
you can use correlated sub query for updating the table.

try this query to update your data table

SQL>update data d set refcounter = (select count(dataid) from versions where dataid = d.datatid);

Re: update using group by [message #188745 is a reply to message #188732] Mon, 21 August 2006 07:24 Go to previous messageGo to next message
hershs
Messages: 5
Registered: August 2006
Junior Member
Thank you,

I tried your SQL. I waitng for 15 minutes for the result and then killed the process.

My data table has 20000 rows, and versions table has about 100000 rows.

Do you have another solution?
Re: update using group by [message #188749 is a reply to message #188745] Mon, 21 August 2006 08:10 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Please try this method. it is bit tedious method but however i believe that it may improve the perfomance..

SQL> create table data(dataid number, refcount number);

Table created.

SQL> create table versions(id number,dataid number);

Table created.

SQL> insert into data values(10,null);

1 row created.

SQL> insert into data values(20,0);

1 row created.

SQL> insert into data values(30,2);

1 row created.

SQL> insert into data values(40,10);

1 row created.

SQL> select * from data;

    DATAID   REFCOUNT
---------- ----------
        10
        20          0
        30          2
        40         10

SQL> insert into versions values(10,10);

1 row created.

SQL> insert into versions values(5,10);

1 row created.

SQL> insert into versions values(1,10);

1 row created.

SQL> insert into versions values(3,20);

1 row created.

SQL> insert into versions values(4,40);

1 row created.

SQL> insert into versions values(17,40);

1 row created.

SQL> insert into versions values(6,20);

1 row created.

SQL> select * from versions;

        ID     DATAID
---------- ----------
        10         10
         5         10
         1         10
         3         20
         4         40
        17         40
         6         20

7 rows selected.

SQL> create global temporary table tem_ver(dataid number primary key , cnt number) on commit preserve rows;

Table created.

SQL> insert into tem_ver select dataid, count(dataid) from versions group by dataid;

3 rows created.


SQL> update (select d.refcount d_refcnt, v.dataid, v.cnt v_cnt  from data d, tem_ver v where d.dataid=v.dataid)
set d_refcnt=v_cnt  2  ;

3 rows updated.

SQL> select * from data;

    DATAID   REFCOUNT
---------- ----------
        10          3
        20          2
        30          2
        40          2

SQL> 



Thanks,
Thangam
Re: update using group by [message #188772 is a reply to message #188732] Mon, 21 August 2006 10:35 Go to previous message
hershs
Messages: 5
Registered: August 2006
Junior Member
gold_oracl:
Great! Thank you very much!
The script took 5 seconds in my DB.
Previous Topic: FRM 40735 Pre Query trigger raised unhandled exception ORA 04067
Next Topic: displaying date in reports using SQL*PLUS
Goto Forum:
  


Current Time: Tue Dec 06 12:12:49 CST 2016

Total time taken to generate the page: 0.05422 seconds