• Welcome, Guest. Please login.
 
October 21, 2019, 02:16:49 am

News:

Welcome to the SQLitening support forums!


Database Maintenance

Started by Fredrick Ughimi, November 22, 2017, 06:45:52 pm

Previous topic - Next topic

Fredrick Ughimi

November 22, 2017, 06:45:52 pm Last Edit: November 22, 2017, 06:49:44 pm by Fredrick Ughimi
Hello,

I am trying to delete duplicate records in tblBioData from HospitalProDB.


#COMPILE EXE
#DIM ALL
#INCLUDE "SQLitening.Inc"

FUNCTION PBMAIN () AS LONG

    slOpen ("HospitalProDB.db3")

    slExe "Begin"
        slEXE "delete from tblBioData where rowid not in(select  min(rowid) from tblBioData group by HospitalNo, PhoneNo)
    slExe "End"
   
   ? "Duplicates Deleted!"

END FUNCTION         


Then try to copy RowID to HospitalNo Column:


#COMPILE EXE
#DIM ALL

#INCLUDE "SQLitening.INC"

FUNCTION PBMAIN () AS LONG

    slOpen ("HospitalProDB.db3")

    slExe "Begin"
        slEXE "update tblBioData set HospitalNo = RowID"
    slExe "End"

    MSGBOX "Column Copied Successfully!", %MB_TASKMODAL OR %MB_ICONINFORMATION, "Mega-Net HospitalPro"

END FUNCTION
                   


I still get error 19 - HospitalNo and PhoneNo not unigue.

Any suggestion would be appreciated
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

No data, no table, no database to test.
Also, nothing to compile.

Fredrick Ughimi

November 23, 2017, 02:55:00 pm #2 Last Edit: November 26, 2017, 03:37:16 am by Fredrick Ughimi
Hello CJ,

I had wanted to attach the database. Electricity issues did not allow me to (I live in Africa).

Anyway here is the attached database.


#COMPILE EXE
#DIM ALL
#INCLUDE "SQLitening.Inc"

FUNCTION PBMAIN () AS LONG

    slOpen ("HospitalProDB.db3")

    slExe BUILD$("Create Table If Not Exists tblBioData(HospitalNo INTEGER primary key autoincrement, Registration TEXT, Title TEXT, Surname TEXT COLLATE NOCASE, Othernames TEXT COLLATE NOCASE,", _
      "DateCreated TEXT, PhoneNo TEXT, Email TEXT, HomeAddress TEXT, OfficeAddress TEXT, Company TEXT, Occupation TEXT, Sex TEXT, Age INTEGER, AgeClass TEXT,", _
      "MaritalStatus TEXT, BirthDate TEXT, Religion TEXT, StateOrigin TEXT, Tribe TEXT, EnroleeNo TEXT, Category TEXT COLLATE NOCASE, Plan TEXT, Entitlements TEXT, EnroleeExpiry TEXT,", _
      "HMOName TEXT, LGA TEXT, BirthPlace TEXT, Nationality TEXT, PixName TEXT, KinName TEXT, KinPhone TEXT, KinEmail TEXT, KinAddress TEXT, Relationship TEXT, Username TEXT, Picture BLOB)")

    slExe "Create UNIQUE Index If Not Exists BioDatandx1 ON tblBioData(HospitalNo, PhoneNo)"

    slExe "Create Index If Not Exists BioDatandx2 ON tblBioData(Surname, Othernames, DateCreated)"


    slExe "Begin"
        slEXE "delete from tblBioData where rowid not in(select  min(rowid) from tblBioData group by HospitalNo, PhoneNo)
        slEXE "update tblBioData set HospitalNo = RowID"
    slExe "End"

    ? "Duplicates Deleted and Column Copied Successfully!"

END FUNCTION
                                         


Regards.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

November 24, 2017, 11:01:30 am #3 Last Edit: November 26, 2017, 03:14:05 pm by cj
Please let me know if this posting is of any help since I'm confused about what to do after removing duplicates.
Please delete the .ZIP file updated because only needed to figure out what you are doing.

Quote
Then try to copy RowID to HospitalNo Column:

HospitalNo and rowid are the same since HospitalNo was defined integer primary key autoincrement

You know how to delete the duplicate entries so not sure what you want to do?
Reorder the rowids which are the same as the HospitalNo?
Your logic correctly deletes duplicate rowid/hospitalno based upon other columns
So, no more duplicate hospital/rowid's
-------------------------------------------------------------------------------------------------------------------
Just assuming what to do for here on and wrote a test program to delete records

Left with rowid 1,3 after removing duplicates in this example and could be made 1,2 which does nothing.

Since all that I can think of doing is reordering the rowids:
copy originaltable to newtable, rename originaltable oldtable, rename newtable originaltable
or easier
for x = 1 to max(rowid)
  if x = hospitalno then incr counter:hospitalno = counter:update
next

If HospitalNo is renamed all other tables pointing to original table will be wrong.

Bottom line,  looks like you did everything correct and not sure what final update is for?

Only wanting unique HospitalNo could be:
After using index to delete duplicates, drop it and redefine as UNIQUE.
CREATE UNIQUE INDEX MyIndexName ON tblBioData(columnX,ColumnY,ColumnZ)
or
Use an ON CONFLICT with original table



Wrote this small example to test removing duplicates using same logic and it works.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
LOCAL s AS STRING
KILL "test.db3"
slOpen "test.db3","C"
slexe "create table t1(HospitalNo integer primary key autoincrement,c2)"
slexe "insert into t1 values (1,'a')"
slexe "insert into t1 values (2,'a')"
slexe "insert into t1 values (3,'b')"
slexe "insert into t1 values (4,'a')"
slexe "insert into t1 values (5,'a')"
slexe "delete from t1 where HospitalNo not in(select min(HospitalNo) from t1 group by c2)"
sql   "select * from t1" 'rows 1 and 3 are unique
END FUNCTION

SUB sql(sSql AS STRING)
DIM sRecordSet() AS STRING
slselAry sSQL,sRecordSet(),"Q9c"
? JOIN$(sRecordSet(),$CR),,sSQL    'result 1 and 3 remaining after deleting duplicates
END SUB

Fredrick Ughimi

Hello CJ,

Thank you for your response.

Quote
HospitalNo and rowid are the same since HospitalNo was defined integer primary key autoincrement


In reality not really. The HospitalNo column was not originally defined that way (integer primary key autoincrement). It was an after thought, it did even work and then abandoned the thought was I read about the negative implications. I just forgot to remove the integer primary key autoincrement part thereafter.

The point I am trying to make is that after removing duplicates, I still get error 19 - HospitalNo and PhoneNo not unigue when I run the code below:


slEXE "update tblBioData set HospitalNo = RowID"
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

November 26, 2017, 11:26:29 am #5 Last Edit: November 26, 2017, 03:11:10 pm by cj
Quote
In reality not really. The HospitalNo column was not originally defined that way (integer primary key autoincrement). It was an after thought, it did even work and then
abandoned the thought was I read about the negative implications. I just forgot to remove the integer primary key autoincrement part thereafter.

The point I am trying to make is that after removing duplicates, I still get error 19 - HospitalNo and PhoneNo not unigue when I run the code below:


Example of how it could occur:
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
KILL "junk.db3"
slOpen "junk.db3","C"
slexe "create table tblBioData(HospitalNo INTEGER,PhoneNo TEXT)"
slexe "insert into tblBioData values(2,'same')"  'no problem will be changed to 1
slexe "insert into tblBioData values(1,'same')"  'later problem, can't  be changed to 1
slExe "Create UNIQUE Index If Not Exists BioDatandx1 ON tblBioData(HospitalNo, PhoneNo)"
slEXE "update tblBioData set HospitalNo = RowID"
END FUNCTION             

cj

This might do what you want

slexe "Alter table tblBioData RENAME TO OriginalTable"
slexe "create table temp AS select * from OriginalTable order by HospitalNo"
slexe "Alter table temp Rename To tblBioData"         

Fredrick Ughimi

I think at this I would need to check if there are duplicate records:


#COMPILE EXE
#DIM ALL

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG

    slOpen "HospitalProDB.db3","C"

    sql "Select * from tblBioData where (HospitalNo, PhoneNo) in (select HospitalNo, PhoneNo from tblBioData group by HospitalNo, PhoneNo having count(*) > 1 )"

END FUNCTION

SUB sql(sSql AS STRING)
DIM sRecordSet() AS STRING
slselAry sSQL,sRecordSet(),"Q9c"
? JOIN$(sRecordSet(),$CR),,sSQL    'result 1 and 3 remaining after deleting duplicates
END SUB             



Getting syntax error with that one.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

Thank you CJ,

This worked.

Quote
This might do what you want

slexe "Alter table tblBioData RENAME TO OriginalTable"
slexe "create table temp AS select * from OriginalTable order by HospitalNo"
slexe "Alter table temp Rename To tblBioData"   
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet