Results 1 to 13 of 13
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: SELECT the last row

    hi
    i want to update a record in a table, but i'm in a situation that maybe 2 row with exactly the same data exist(no primary key), but i know that the row (that i want to update) is the last record, is there any statement that select the last? or any substitute solution?

    Code:
    UPDATE `cars` 
    SET `model` = 'Z4',
    WHERE ...

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    What defines last row? This isn't a text file, your row could be any point in the database and not in the last position just because it was the last row entered.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    No, as far as I know, there is no solution. Just as guelphdad said, there is no order in the database other than an order you create by yourself.

    You should really have a primary key.

    You could also add the insert date if you want to keep the old version of rows, but ADD A PRIMARY KEY... ADD A PRIMARY KEY... ADD A PRIMARY KEY...


    Regards,

    RBARAER

  4. #4
    Join Date
    Jul 2003
    Posts
    73

    Try Limit

    Had a quick look at the manual - here - and LIMIT might help.

    Although I don't have MySQL at work - I guess you could do it as follows:

    UPDATE table
    SET blah
    WHERE condition
    ORDER BY whatever DESC
    LIMIT 1

    The only problem is that if you do have two rows of exactly the same data - I'm not sure if the ORDER BY (anyrow) DESC will actually pick up the last one, but you can give it a shot.

    I will also echo the fact that a primary key is a good idea.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  5. #5
    Join Date
    Jun 2004
    Posts
    75

    Question Oh Dear.....

    Sorry, but you all seem to have missed the mark by a wide margin.

    There is no reason to have two rows in any table with identical data.
    Please refer to any book on the fundamentals of database design.

    The point is that to update any single record in any table in any database
    not only is it a good idea, it is essential to be able to uniquely identify each record hence one of the primary reasons for having a primary key.

    The solution to your problem is to delete all duplicated records and reenter them so that there is only one row for each set of data then modify you table to incorporate a primary key.

    Again sorry to be blunt

    Shaun McGuile

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello Shaun,

    Of course, you're right, the notion of primary key is essential in any database design.

    This being said, excuse me, but even if you may have put it in a better way than I did, my

    ADD A PRIMARY KEY... ADD A PRIMARY KEY... ADD A PRIMARY KEY...
    was in my opinion clear enough on this point.

    So, I think your

    Sorry, but you all seem to have missed the mark by a wide margin.
    seems a LOT EXAGERATED to me, isn't it ?

    Yes I feel like you were BLUNT.

    Regards,

    RBARAER

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Sorry, posted twice...

  8. #8
    Join Date
    Jul 2003
    Posts
    73
    Quote Originally Posted by bono56
    i want to update a record in a table, but i'm in a situation that maybe 2 row with exactly the same data exist(no primary key)...
    From what I could gather - bono56's problem was that he had a problem with his table, that he hoped to rectify by deleting one of two identical rows.

    While everyone that posted here said that a Primary Key is required (even bono56 himself) - we were trying to assist his problem (delete one of two identical rows) - not chastising him for being in that situation to begin with.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  9. #9
    Join Date
    Jun 2004
    Posts
    75

    Red face Apologies to RBARAER

    I suffered from "not reading all messages fully syndrome".

    Sorry

    "Fix the problem not the blame!"
    Last edited by Shaun McGuile; 10-06-04 at 06:59.

  10. #10
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Thanks for your apologies, Shaun.

    Now, Joel, you're right when saying that we are here to help bono56. That is just what we are doing. I agree to the fact we may have been direct. However, the solution Shaun gave :

    The solution to your problem is to delete all duplicated records and reenter them so that there is only one row for each set of data then modify you table to incorporate a primary key.
    is the right one. Or at least, I don't know of a better one.

    To do so, I suggest this :

    Code:
     -- create a "temporary" table
    CREATE TABLE temp_table AS SELECT * FROM your_current_table;
     -- deletes all records from your current table
    TRUNCATE TABLE your_current_table;
    -- add the primary key constraint
    ALTER TABLE your_current_table ADD CONSTRAINT cp_your_current_table PRIMARY KEY(field1, field3, ...);
    -- re-insert data, now with distinct values
    INSERT INTO your_current_table SELECT DISTINCT(field1, field2,...) FROM temp_table; -- be sure to select all fields from temp_table
    COMMIT;
    -- drop the "temporary table"
    DROP TABLE temp_table;
    Or, in case you want to keep ALL the records you have, you can add an auto-increment integer key :

    Code:
     -- create a "temporary" table
    CREATE TABLE temp_table AS SELECT * FROM your_current_table;
     -- deletes all records from your current table
    TRUNCATE TABLE your_current_table;
    -- add the new field
    ALTER TABLE your_current_table ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT;
    -- add the primary key constraint
    ALTER TABLE your_current_table ADD CONSTRAINT cp_your_current_table PRIMARY KEY(id);
    -- re-insert data, now with distinct values
    INSERT INTO your_current_table SELECT * FROM temp_table;
    COMMIT;
    -- drop the "temporary table"
    DROP TABLE temp_table;
    I hope it will help.

    Regards,

    RBARAER

  11. #11
    Join Date
    Sep 2004
    Posts
    39
    Quite an intense place isn't it

    I beg to disagree ... formally and to agree ... technically

    A table can have dozens of rows containing exactly the same data : say I sell one item which has one reference number, one price, one size, one ... whatever and I have a client who daily buys a couple of them ... Would that not create dozens of rows in my "sales" table containing the same data?

    Technically however it would be "better" to have a primary key. Of course.

    However ... (again) ... if our friend has two rows containing exactly the same data why would he > specifically < need to alter the last one ? I wonder

    Have fun
    One line at a time

  12. #12
    Join Date
    Jul 2003
    Posts
    73
    I assumed that he accidentally added the same row twice (or something similar) - and wishes for the "last" one to be modified to something else.

    But yeah - as the two rows are exactly the same - it won't matter which one you update. Unless you are selecting the rows without an ORDER BY clause - and don't want the current order to be modified. Correctly me if I'm wrong, but the default order by depends on the order the rows were added (making an update of the "last" row necessary).
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there is no such thing as a default order

    either you say ORDER BY or you don't

    if you don't, then the order of rows is unpredictable

    yes, it appears to be in fifo order, but that's a coincidence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •