Deleting duplicates on SQLBase

Question

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?

score 2 · Accepted Answer · edited Aug 19 '16 at 21:15

2

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)
/

edited Aug 19 '16 at 21:15

marc_s

732,580
175
1,330
1,459

answered Oct 12 '15 at 13:34

ZephyCZ

71
4

This is true, if I would create a programm than this would be the case to solve this. But since there is no possibility to do this on the existing program, I have to handle this by simply using sqltalk. May you know how to use stored procedures in sqltalk? – Dwza Oct 12 '15 at 15:57
I added example for storage procedure, see above. This you can run in SqlTalk. Try her. – ZephyCZ Oct 13 '15 at 06:44
I have to try this tomorrow. SqlTalk supports Centura Functions ? btw, actually there is no need for commit with a prepare and execute. – Dwza Oct 13 '15 at 08:01
Yes, Sal Functions are in SqlBase supported. This procedure You can run in connected SqlTalk as is. Commit is not needed? This is possible, my primary DB is Oracle, there is necessary :-). – ZephyCZ Oct 13 '15 at 09:29
how do I call the procedures than ? – Dwza Nov 02 '15 at 15:36
1) open SqlTalk, 2) connect to your database, 3) insert this procedure into SqlTalk, 4) run with CTRL+ENTER – ZephyCZ Nov 03 '15 at 07:19
...or you can store procedure into database and then you can run "execute procedureName", example here: http://goo.gl/BVtvKq – ZephyCZ Nov 03 '15 at 07:48

score 0 · Answer 2 · answered Nov 02 '17 at 23:02

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,, /

score -1 · Answer 3 · answered Jan 02 '17 at 15:52

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 );

Deleting duplicates on SQLBase

3 Answers3