MySQL itself doesn't mind about having no primary key:
mysql> desc test_table;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| Actual ID | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
mysql> select * from test_table where `Actual ID` = 'My actual ID';
+--------------+
| Actual ID |
+--------------+
| My actual ID |
| My actual ID |
| My actual ID |
| My actual ID |
| My actual ID |
| My actual ID |
+--------------+
6 rows in set (0.00 sec)
mysql> delete from test_table where `Actual ID` = 'My actual ID' limit 2;
Query OK, 2 rows affected (0.13 sec)
mysql> select * from test_table where `Actual ID` = 'My actual ID';
+--------------+
| Actual ID |
+--------------+
| My actual ID |
| My actual ID |
| My actual ID |
| My actual ID |
+--------------+
4 rows in set (0.00 sec)
If the records are *identical*, it *can't* matter which ones you
actually delete!
Even in Gambas, I don't run into any problems specifically to do with
having no Primary Key:
Try myDB.Open
If Error Then
Message("Cannot Open database:" & Error.Text)
Return False
End If
myDB.Exec("delete from test_table")
For n = 1 To 6
myDB.Exec("insert into test_table values ('My actual ID')")
Next
res = myDB.Exec("select * from test_table")
counter = 0
res.MoveFirst
While res.Available
counter += 1
Print Str(counter) & ": " & res["Actual ID"]
res.MoveNext
Wend
myDB.Exec("delete from test_table where `Actual ID` = 'My actual ID'
limit 2")
res = myDB.Exec("select * from test_table")
counter = 0
res.MoveFirst
While res.Available
counter += 1
Print Str(counter) & ": " & res["Actual ID"]
res.MoveNext
Wend
The above code works perfectly, giving the expected results:
1: My actual ID
2: My actual ID
3: My actual ID
4: My actual ID
5: My actual ID
6: My actual ID
1: My actual ID
2: My actual ID
3: My actual ID
4: My actual ID
Willy, can you explain again EXACTLY what your problem is? What error
do you get, where, and when... ?
Kind regards,
Caveat
On 05/10/13 14:08, Willy Raets wrote:
> On Sat, 2013-10-05 at 10:08 +0200, Fernando Martins wrote:
>> On 10/05/2013 01:25 AM, Willy Raets wrote:
>>> On Fri, 2013-10-04 at 22:53 +0200, Willy Raets wrote:
>>>
>>> Nando, Fernando and Caveat,
>>>
>>> Thanks for your responses.
>>>
>>>> Content in 'ICzakgewicht' can be like this:
>>>> | ID | Gewicht |
>>>> 345 100
>>>> 345 100
>>>> 345 50
>>>> 347 200
>>>> 347 200
>>>> 347 200
>>>> 347 200
>>> @ Fernando and Caveat
>>>
>>> Above you can see that more than one record can be exactly the same.
>>> At any time only one of them needs to be removed.
>>> With Gambas this can be done, looking for the first record meeting the
>>> criteria, delete it, next update and leave the table.
>>>
>>> With SQL DELETE all records that meet the criteria will be deleted
>>> instead of only one.
>>>
>>> That is why this is not an option.
>>>
>>>
>> "duplicate rows are and always were a mistake in SQL", C.J.Date
>>
>> http://books.google.nl/books?id=y_eVBB5qdwMC&pg=PA159&lpg=PA159&dq=c+j+date+duplicate+records&source=bl&ots=DjN-LDuU2B&sig=5-vlJ8itEkC7h6aFMt2PxHkT-ug&hl=en&sa=X&ei=1cdPUtjSCcHH0QWvooGQCw&redir_esc=y#v=onepage&q=c%20j%20date%20duplicate%20records&f=false
>>
>> In my experience, most often there is a design problem with the database
>> allowing for duplicates. With duplicates, you step out of the set theory
>> behind relational dbs and enter into trouble. I admit I have done it
>> myself, but best be avoided.
>>
> Then adding a primary key should solve that problem.
>
------------------------------------------------------------------------------
October Webinars: Code for Performance
Free Intel webinars can help you accelerate application performance.
Explore tips for MPI, OpenMP, advanced profiling, and more. Get the most from
the latest Intel processors and coprocessors. See abstracts and register >
http://pubads.g.doubleclick.net/gampad/clk?id=60134791&iu=/4140/ostg.clktrk
_______________________________________________
Gambas-user mailing list
Gambas-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/gambas-user
