0

I want to delete a row from one of my .sql tables by a user defined integer (1 through rowCount()). The below is pseudocode, but it illustrates what I want I think.

$i = 1; //example
$quedb = $db->query("
    DELETE *
        FROM table
        WHERE ROWNUMBER = '$i'
");

Is there a way to do this within the SQL environment? I don't want to delete a row based on a specific element (a friend of mine suggested querying for an element in the row I define, but I just want to delete the nth row, where n is user defined).

gator
  • 3,465
  • 8
  • 36
  • 76
  • 2
    nth row in what sense? like a row having "id" field as a certain n value? We may not be able to tell database to delete 10th or 20th row in order of how they are stored, we have to provide a certain field name criteria – Hanky Panky Jan 02 '13 at 05:32
  • I was hoping there'd be an easy solution. By nth row I mean "delete the first row in the table" or "delete the 2nd row" or etc etc "delete the nth row". – gator Jan 02 '13 at 05:33
  • 1
    If you have an id field that specifies the order of how rows are inserted, most probably an auto increment, then its easy to tell. But if there is no primary or no unique id field, or probably timestamp then its tough to tell databse which nth row. We cant tell it that nth row as stored in file system – Hanky Panky Jan 02 '13 at 05:35
  • I was thinking the same before I constructed the database, but auto-incrementing as new rows are added is troublesome, at least in my experience. Say I have 5 rows in my table, with an id field auto-incrementing (so 1, 2, 3, 4, 5); if I delete row 3, it doesn't adjust the entire table so I'm left with gaps in my table (eg. 1, 2, 4, 5). I'm a beginner at this, so it's surely a matter of me missing something. – gator Jan 02 '13 at 05:36
  • 1
    Thats expected behaviour for consistency. You would not want all the ids to be adjusted if you delete one. Because what if those ids are used as Foreign keys in another table? Then all the data consistency will be lost in an instant. So those 'gaps' are good – Hanky Panky Jan 02 '13 at 05:38
  • Your logic is sound, but what I'm working on does not use a theoretical id field in other tables. – gator Jan 02 '13 at 05:41
  • 1
    Then you would need to write some code for managing ids yourself and after every delete reconfigure the ids. Although this is not advisable and probably dangerous too, bust just for the sake of learning. Also see this post http://stackoverflow.com/questions/2214141/mysql-auto-increment-after-delete – Hanky Panky Jan 02 '13 at 05:44
  • Why nth row ? You are already passing ID to delete a row ? – Sahal Jan 02 '13 at 06:08

2 Answers2

1

You shouldn't do that.
"nth row" is a nonsense in the context of databases.
Database is something different from lists you're familiar to. They have no predefined order at all. Database is an abstract heap of rows whose take order only at select time, always different, based on the field(s) chosen to order.

To identify a row you have to use unique identifier, which invented to serve the very purpose.
So, add id auto_incremented primary key field to your table and use it to identify the row. "Other tables" are not only reason to keep consistency. Your own links on the site require consistent addressing too, no matter if you added or deleted some rows.

If you want to enumerate your output, do it at select time, using PHP. That's the only proper way.

Please, before inventing your own wheel, learn the very basics of database design.
Or at least follow good advises from more experienced people.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • You're absolutely right. I didn't want to do it this way because I'd have to rewrite a good chunk of code, but I guess it is worth it in the long term. – gator Jan 02 '13 at 06:11
-1

You can do that using a nested query. Like so...

Delete from table where id in company(Select id from table limit 5,1)

But it is really not recommended as the behavior is not very consistent.

iWantSimpleLife
  • 1,944
  • 14
  • 22