SQL Record deletion

[BrutalSnyper]

Hi there,

In my app, I have a part that deletes a record in my SQL database, but it also deletes records with the same value.

Example:

Names
Sam
John
Sam
Neil

If I delete one of the records containing the value "Neil", both of the records containing the value Neil are deleted.

How could I stop this from happening?
I thought about having a second field in my table and giving each field an ID, but this would cause bigger problems later.

 

[klaus]

What are the other fields ?
How do you select the record to delete ?

I thought about having a second field in my table and giving each field an ID,

Thats the best way.

but this would cause bigger problems later.

What problem ?

Best regards.

 

[BrutalSnyper]

What problem ?

The problem I see appearing is that say I have 4 records in my database, each with an ID from 1-4:

1. This
2. Is
3. My
4. Example

And I delete record 3, then the records will be 1,2,4 which will cause me more trouble if I decide to do something such as restrict the amount of records that can be entered.

 

[klaus]

The advantage of the ID is that it's UNIQUE !
In SQLite you can get the number of records.
In my DB applications when I read a database and show it in a table I define in parralel a List with the ID indexes to have a relationship between the rows and the IDs. This is valid for the whole database and also if it's filtered.

Best regards.

 

[ValDog]

So, once I initialize a database table how do I go about getting the number of records in the table?

 

[mc73]

You can use a select count(*) or read a cursor's row count.