| Author |
Topic |
|
Mister
Starting Member
4 Posts |
 Posted - 2007-09-10 : 21:04:53
|
| Hello - I inserted a row and now need to delete it. Is there a simple query to delete the last row inserted into a table?Thanks for your help! |
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-10 : 21:09:57
|
Do you not know what you inserted?
Future guru in the making. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-09-10 : 21:36:48
|
Do you have a date time column in the table that record when a row was inserted ? or do you have identity column ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Mister
Starting Member
4 Posts |
Posted - 2007-09-10 : 22:22:54
|
| I entered data into several columns in the row using the Insert Into function. The dates I entered did not insert in the correct format. I guess I could just update the columns in that row, but I'm not sure how to get the correct date format. |
|
|
|
Zoroaster
Aged Yak Warrior
702 Posts |
Posted - 2007-09-10 : 22:30:51
|
quote:
Originally posted by Mister
I entered data into several columns in the row using the Insert Into function. The dates I entered did not insert in the correct format. I guess I could just update the columns in that row, but I'm not sure how to get the correct date format.
What format do you need the date in?
Future guru in the making. |
|
|
|
Mister
Starting Member
4 Posts |
Posted - 2007-09-10 : 22:35:13
|
quote:
Originally posted by Zoroaster
quote:
Originally posted by Mister
I entered data into several columns in the row using the Insert Into function. The dates I entered did not insert in the correct format. I guess I could just update the columns in that row, but I'm not sure how to get the correct date format.
What format do you need the date in?
Future guru in the making.
I entered 11/01/07 and 1900-01-02 00:00:00.000 was entered. So would that be DateTime? |
|
|
|
Vijaykumar_Patil
Posting Yak Master
121 Posts |
Posted - 2007-09-11 : 01:16:45
|
| That is because you missed the year..you would need to enter 2007 and not 07. That is the cause of your problem. And in regards to format look at BOL convert and cast functions.Necessity is the mother of all inventions! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-11 : 01:35:13
|
| "Is there a simple query to delete the last row inserted into a table?"Well, in a relational database there isn't really any concept of "the last row inserted". The data is in no particular order unless you use an ORDER BY to sort it (when you Select it), or you specify explicitly some criteria in a WHERE clause.So if you have some Unique characteristics of the record you added then you can Delete it.So try something likeSELECT *-- DELETE DFROM MyTable AS DWHERE MyDate = '19000102 00:00:00.000'and see if that displays the record you added. Once you display the required record (and only that one!) then remove the SELECT line and remove the comment from the DELETE line and you can delete it. Check that the delete says that it processed ONLY ONE ROW! Otherwise restore from backup. (Make a backup first pls!)Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-11 : 01:36:11
|
| "That is because you missed the year..you would need to enter 2007 and not 07"That's news to me!! unless I misunderstood what you meant?SELECT CONVERT(datetime, '11/01/07')gives me:2007-11-01 00:00:00.000which looks about right to me.Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-11 : 01:47:16
|
He missed the leading and trailing ' and thus 11/01/07 is treated like math11/01 = 1111/07 = 11 equals '19000102 00:00:00'
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-11 : 01:51:33
|
"He missed the leading and trailing ' "Ah! That would do it ...  |
|
|
|
|
How to delete last row in a table