Delete row in mysql

Question

I am building JSP application in which I want to do following operations on specific table

• Display Data
• Delete Data

I've done displaying data, however:

How can I delete a row in database if the table does not have any primary key ? (Delete operation does not depends on any value of that row)

Suppose here is may table -->> mytemp Here is the data

Name | RollNo
ABC  |  98
XYZ  |  76
ABC  |  98
XYZ  |  76

There is no key in this table and i want to delete 3 rd record. How can i do this ?

Answer 1

You can choose any available column you think most suitable: e.g.

DELETE FROM table_name WHERE column_name  = 'valuse'    

without column_name you can delete all rows only.

column_name does not has to be primary key, but all rows with column_name = 'valuse' will be deleted.

---

EDIT
To delete:

  DELETE FROM table_name WHERE Name = 'ABC' AND  RollNo  = 98;       

Name and RollNo may not be primary key.

Delete only third row:

DELETE FROM table_name  
WHERE ('ABC',98) IN ( SELECT TOP 1 Name, Rollno 
                      FROM table_name 
                      ORDER BY Name,RollNo DES)   

Second way: if TOP not works

DELETE FROM table_name  
WHERE ('ABC',98) IN ( SELECT  Name, Rollno 
                      FROM table_name 
                      ORDER BY Name,RollNo DES LIMT 1)   

CAUTION: it will delete one, which one I am not sure.

Give it a try!!

Answer 2

Try this:

DELETE t
FROM YourTable t
WHERE t.Name    = 'Selected Name'
  AND t.RollNo = 'Selected RollNo';