| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-10 : 08:59:20
|
| Gurvinder writes "I want to delete the first row of my database. I obviously have no unique key, although this might be a simple question, but I am confused and have no clue of how to do this. Please help." |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-10 : 09:27:37
|
| You can't.There is no concept of first or last in relational theory, barring an order by cluase. And given your lack of a PK, I don't see what you would order by....<O> |
 |
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2002-07-10 : 09:29:20
|
| When you say database i take it you mean the first row of a table within the database.If you have no unique key then how do you know which is the first row ? Does it have a datetime field in it ? Why has it got no unique key anyway ? Can you order it by a certain field to ascertain the first record.I am confused alsoPaul |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-07-10 : 09:34:41
|
| In the absence of an ORDER BY, the first physical row will be the one that is deleted. If a clustered index is present, it will be the lowest value tuple pointed to by the index, But in the absence of and sort order, deletion is a mystery. You might want to look at this logic...DELETE FROM TABLENAMEwhere col = (SELECT TOP 1 COL FROM TABLENAME [ORDER BY ORDERCOL ASC/DESC])That will delete one row, but you must specify the order column to sort by. Indexing should speed up the delete on a large table.Daniel Small MIAPwww.danielsmall.com IT Factoring |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-10 : 09:43:26
|
If there is no unique key, then there is no guarantee that the subquery in Danny's DML will return a unique column value:quote:
DELETE FROM TABLENAME where col = (SELECT TOP 1 COL FROM TABLENAME [ORDER BY ORDERCOL ASC/DESC])
and therefore running this query will likely delete more than one row. Bottom line is there is no relational concept of "first".Jonathan Boott, MCDBAEdited by - setbasedisthetruepath on 07/10/2002 10:50:03 |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-07-10 : 10:11:17
|
| This is not education in relational algebra. The Guy simply wants to delete 1 row. Taking your comment on board set the row count, i.e.SET ROWCOUNT 1DELETE FROM TABLENAME where col = (SELECT TOP 1 COL FROM TABLENAME [ORDER BY ORDERCOL ASC/DESC]) Time to move on now...Daniel Small MIAPwww.danielsmall.com IT Factoring |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-10 : 10:41:21
|
quote:
This is not education in relational algebra....Time to move on now...
Well Danny, that's what we try to do here. When people are misinformed about the details of relational databases we try to educate them.And the truth is, we're doing YOU a favor too...because if they try your advice and end up deleting the wrong row, who do you think they're gonna complain to? Doesn't matter if you gave sound advice or not.And this person definitely needs some education in relational algebra/theory, because they wouldn't have asked this question if they had enough now. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-10 : 10:44:10
|
| Danny, relax.This is a free discussion forum visited by newbies and pollinated by SQL Server professionals like you and I. I've read your contributions on many other posts and I don't doubt your capabilities.One aspect of sqlteam that makes it so attractive is the high level of discourse. Someone might indeed ask how to delete 1 row, or the "first" row, and while it might be more efficient to simply provide an answer, it is infinitely more valuable to suggest that it is a poor question and not expressed in the proper way. Doing it that way educates the poster and brings them back with more complex questions and that raises the quality of sqlteam overall, which is why any of us are here in the first place.Jonathan Boott, MCDBA |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-07-10 : 10:51:12
|
| You are right. My problem is that I am quite impatient. Driven by increasingly time restrictive deadlines.Daniel Small MIAPwww.danielsmall.com IT Factoring |
|
|
|
|
deleting 1st row