Re: [SQL] Mail about duplicate rows

Dear Jackson and Michael,

Thanks very much for your help the query worked. Also can you help in one
more way. It took a lot of time to update the radius table around 5 minutes, the
size of the table was around 360 KB's only, so could you suggest a way of
improving the performance since if i am having a table of around 120 - 165 MB it
does not take much time to update the radius table.

Thanks for your help once again.

Regards,
Vicky

"Jackson, DeJuan" wrote:

> Then you would use:
> INSERT INTO radius
> SELECT * FROM raduistemp r1
> WHERE NOT EXISTS(SELECT 1 FROM radius r2
> WHERE r1.uname=r2.uname AND r1.logdate=r2.logdate AND
> r1.logtime=r2.logtime);
> -DEJ
>
> > -----Original Message-----
> > From: Vikrant Rathore [SMTP:vikrant(at)chemquick(dot)com]
> > Sent: Monday, June 07, 1999 12:37 AM
> > To: Fomichev Michael
> > Cc: pgsql-sql(at)postgreSQL(dot)org
> > Subject: Re: [SQL] Mail about duplicate rows
> >
> > Thanks for your help. But as you know the UNAME is not unique in radius
> > logs. The
> > primary key for this table is (Uname,logdate,logtime).
> >
> > So this three fields together forms a primary key of the table.
> >
> > Thanks & regards,
> > Vicky
> >
> >
> > Fomichev Michael wrote:
> >
> > > On Mon, 7 Jun 1999, Vikrant Rathore wrote:
> > >
> > > > I am trying to append a table radius from another table radiustemp,
> > both
> > > > having the same structure, but want to append only those tuples from
> > > > radiustemp which are not there in radius. The size of the table can be
> > > > up to 165 MB.
> > > > So anyone can suggest me a better way of doing this.
> > > >
> > > > The structure if table is like this .
> > > >
> > > > Table = radius
> > > >
> > +----------------------------------+----------------------------------+---
> > ----+
> > > >
> > > > | Field | Type
> > |
> > > > Length|
> > > >
> > +----------------------------------+----------------------------------+---
> > ----+
> > > >
> > > > | uname | char()
> > > > | 256 |
> > > > | logdate | date
> > > > | 4 |
> > > > | logtime | time
> > > > | 8 |
> > > > | duration | int4
> > > > | 4 |
> > > > | status | char()
> > > > | 20 |
> > > > | nasadd | char()
> > > > | 20 |
> > > > | port | int4
> > > > | 4 |
> > > > | bytesin | int4
> > > > | 4 |
> > > > | bytesout | int4
> > > > | 4 |
> > > > | packin | int4
> > > > | 4 |
> > > > | packout | int4
> > > > | 4 |
> > > > | misc | int4
> > > > | 4 |
> > > >
> > +----------------------------------+----------------------------------+---
> > ----+
> > > >
> > > > Thank's in advance for your help.
> > > >
> > > > Thanks & regards,
> > > > Vicky
> > > >
> > > insert into RADIUS select * from RADIUSTEMP where UNAME not in (select
> > > UNAME from RADIUS)
> > >
> > > UNAME must be unique. If it is not, than you need to use another field
> > > (for example "code") which will be unique.
> > >
> > > P.S. Nice book about SQL: "Understanding SQL" by Martin Gruber. ISBN
> > > 5-85582-010-6
> > >
> > > @------------------+-----------------------------------------------@
> > > | Fomichev Mikhail | The Government of Kamchatka region. |
> > > | Vladimirovich | The Labour and Social Development Department. |
> > > |<fomichev(at)null(dot)ru>| |
> > > @------------------+-----------------------------------------------@
> >