SELECT the last row

bono56 · #1 (**permalink**) 10-04-04, 20:05

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

guelphdad · #2 (**permalink**) 10-05-04, 09:37

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.

RBARAER · #3 (**permalink**) 10-05-04, 10:13

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

joeldixon66 · #4 (**permalink**) 10-05-04, 20:11

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.

Shaun McGuile · #5 (**permalink**) 10-06-04, 03:34

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

RBARAER · #6 (**permalink**) 10-06-04, 05:06

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

Quote:

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

Quote:

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

RBARAER · #7 (**permalink**) 10-06-04, 05:34

Sorry, posted twice...

joeldixon66 · #8 (**permalink**) 10-06-04, 05:50

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.

Shaun McGuile · #9 (**permalink**) 10-06-04, 05:53

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

Sorry

"Fix the problem not the blame!"

RBARAER · #10 (**permalink**) 10-06-04, 10:45

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 :

Quote:

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

JohnStrecker · #11 (**permalink**) 11-10-04, 18:48

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

joeldixon66 · #12 (**permalink**) 11-10-04, 22:38

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).

r937 · #13 (**permalink**) 11-11-04, 06:17

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

Internet Services

Web Development

Digital Marketing

Consumer Tech