Thread: Mail about duplicate rows
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
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@null.ru>| | @------------------+-----------------------------------------------@
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@null.ru>| | > @------------------+-----------------------------------------------@