CODEWITHSUNDEEP
guptateamdevelopersqlbasecentura
Dwza
Dwza

Reputation: 6565

Deleting duplicates on SQLBase

Im currently running a SQLBase database and tried several was to select and delete duplicate items.

Here a scenario:

Tablename: test1

||=========||=========||==================||
|| column1 || column2 || rowid            ||
||=========||=========||==================||
||    1    ||    1    || AAAAAAAAAAAAAAAA ||
||    1    ||    1    || BBBBBBBBBBBBBBBB ||
||    1    ||    1    || CCCCCCCCCCCCCCCC ||
||    1    ||    2    || DDDDDDDDDDDDDDDD ||
||    1    ||    2    || EEEEEEEEEEEEEEEE ||
||    1    ||    3    || FFFFFFFFFFFFFFFF ||
||    1    ||    4    || GGGGGGGGGGGGGGGG ||
||=========||=========||==================||

RowID is a virtual column. This gets created by the system.

What I want to do is delete all duplicates so I end up with:

||=========||=========||==================||
|| column1 || column2 || rowid            ||
||=========||=========||==================||
||    1    ||    1    || AAAAAAAAAAAAAAAA ||
||    1    ||    2    || DDDDDDDDDDDDDDDD ||
||    1    ||    3    || FFFFFFFFFFFFFFFF ||
||    1    ||    4    || GGGGGGGGGGGGGGGG ||
||=========||=========||==================||

The problem is, its in SQLBase and there is no function that iterates my duplicates.

I find duplicates like:

SELECT column1, column2 COUNT(*) 
FROM test1 
GROUP BY column1, column2 
HAVING COUNT(*) > 1;

And there is my problem. Can't find a way to delete them from that point on. Also I obviously can't add the rowid to the duplicate select because of the group by statement.

Are there any possibilitys delete the duplicates so having only one entry each combination of column1 und column2?

Upvotes: 0

Views: 291

Answers (3)

Steve Leighton
Steve Leighton

Reputation: 840

You can use SAL and Sql statements in a SQLBase Stored Procedure. As per the example given by ZephyCZ. To retrieve and execute a stored procedure in one step from SQLTalk, use the EXECUTE command. This command accepts input values and retrieves data ( if needed ) as well as executes the stored procedure; for example:

EXECUTE delDuplicityTest1 \ 1,50,, /

Upvotes: 0

Ralf
Ralf

Reputation: 39

For those purposes SqlBase is not as tolerant as other DBMS.

The easiest way is to auto-generate your delete statements.

select 'delete from test1 where rowid = ''' || a.rowid ''';' from test1 a where a.rowid not in ( select max (x.rowid) from test1 x group by x.column1, x.column2 );

Upvotes: -1

ZephyCZ
ZephyCZ

Reputation: 71

For find rows to delete, you can use better select:

select a.rowid 
from test1 a 
where a.rowid not in 
(
   select max (x.rowid)
   from test1 x
   group by x.column1, x.column2
);

Unfortunately, does not apply:

delete
from test1 a 
where a.rowid not in 
(
   select max (x.rowid)
   from test1 x
   group by x.column1, x.column2
);

But you can delete this with two sql handle:

Call SqlPrepareAndExecute(hSql1, 'select a.rowid from test1 ...(like above)...into :sRowid')
While SqlFetchNext(hSql1, nFetch)
   Call SqlPrepareAndExecute(hSql2, 'delete from test1 where rowid = :sRowid')

Example as Sql Base stored procedure:

Procedure: delDuplicityTest1
Parameters
Local Variables
   Sql Handle: hSql1
   Sql Handle: hSql2
   String: sSelect
   String: sRowid
   Number: nFetch
Actions
      Call SqlConnect(hSql1)
      Call SqlConnect(hSql2)
      Set sSelect = 'select a.rowid 
                     from test1 a 
                     where a.rowid not in 
                     (
                        select min (x.rowid)
                        from test1 x
                        group by x.column1, x.column2
                     )
                     into :sRowid '
      Call SqlPrepareAndExecute(hSql1, sSelect)
      While SqlFetchNext(hSql1, nFetch)
         Call SqlPrepareAndExecute(hSql2, 'delete from test1 where rowid = :sRowid')

      Call SqlCommit(hSql2)
      Call SqlDisconnect(hSql1)
      Call SqlDisconnect(hSql2)
/

Upvotes: 2

Related Questions