3

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 ?

Nishant Nawarkhede
  • 8,234
  • 12
  • 59
  • 81
  • `(Delete operation does not depends on any value of that row)` So, On what column('s) the delete operation depends on? How the user suppposed to choose the rows to delete from the displayed data? – Mahmoud Gamal Dec 27 '12 at 10:01
  • You need someway to identify a row to delete or do anything with.... never mind the fact that it's scary you have no primary key.... – Jon Clements Dec 27 '12 at 10:02
  • try this post :: http://stackoverflow.com/questions/2728413/equivalent-of-oracles-rowid-in-mysql – Ajith Sasidharan Dec 27 '12 at 10:44
  • @JonClements I agree with you **"You need someway to identify a row to delete or do anything with"** , But how it works in GUI or IDE ?? – Nishant Nawarkhede Dec 27 '12 at 10:45
  • 1) What does this mean - ___But how it works in GUI or IDE ??___, 2) do you really think it matters if you delete the third one or the first one? 3) Can you give a use-case as to where you will need such a thing? – Prakash K Dec 27 '12 at 11:43

3 Answers3

4

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

Grijesh Chauhan
  • 57,103
  • 20
  • 141
  • 208
1

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

NoNaMe
  • 6,020
  • 30
  • 82
  • 110
1

Try this:

DELETE t
FROM YourTable t
WHERE t.Name    = 'Selected Name'
  AND t.RollNo = 'Selected RollNo';
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • Thank you very much . If there are more than one row having same value , then they also got deleted , i dont want do like this. – Nishant Nawarkhede Dec 27 '12 at 10:37
  • @Nishant - Yes it would. However you can limit that adding `LIMIT n` to limit the number of rows deleted by n. – Mahmoud Gamal Dec 27 '12 at 10:38
  • Thanks , suppose there are three records like **XYZ 98** , **ABC 98** ,**XYZ 98**,**XYZ 98** , I want to delete third record , How can i set limit over here ? – Nishant Nawarkhede Dec 27 '12 at 10:40
  • 2
    @Nishant But what is your condition in this case if it is `WHERE name = 'xyz' and no = 89` then you can put `limit 1` this will delete only one row, but it doesn't matter for you which row. Because rows has no significant order in the table. They are not stored in a specific order. – Mahmoud Gamal Dec 27 '12 at 10:46