CODEWITHSUNDEEP
mysqldatabasejsp
Nishant Nawarkhede
Nishant Nawarkhede

Reputation: 8400

Delete row in mysql

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

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 ?

Upvotes: 3

Views: 9320

Answers (3)

Grijesh Chauhan
Grijesh Chauhan

Reputation: 58271

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!!

Upvotes: 4

NoNaMe
NoNaMe

Reputation: 6222

If you want to delete all records you can use

delete from TABLE_NAME

and if you have any difference in the rows in the DB you can add where clause as well like

delete from TABLE_NAME where COL1=XXX AND/OR COL@ =YYY

etc...

delete from TABLE_NAME where RollNo= RollNo_HERE AND Name = 'NAME_HERE'

Hope this will help you

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Try this:

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

Upvotes: 1

Related Questions