Back to General and Gameplay Programming

Updating a specified row in MySql

General and Gameplay Programming Programming

Started by eq May 07, 2007 09:45 AM

8 comments, last by Wan 16 years, 11 months ago

656

Author

May 07, 2007 09:45 AM

The following code creates a table without any indices, hence it can contain the same string:


create table Test (Name VARCHAR(30));
insert into Test (Name) VALUES ('Test1');
insert into Test (Name) VALUES ('Test2');
insert into Test (Name) VALUES ('Test3');
insert into Test (Name) VALUES ('Foo');
insert into Test (Name) VALUES ('Foo');
insert into Test (Name) VALUES ('Foo');
insert into Test (Name) VALUES ('Bar');
select * from Test;

Now let's say that you'd like to change Test3 into a foo, this is simple


update Test set Name='Foo' where Name='Test3';
select * from Test;

But how would you go about changing the last 'Foo' to 'Bar'? It's easy enough to select the correct row using the LIMIT command:


select * from Test limit 5,1

But for the update command the LIMIT only takes a row count, i.e:


update Test set Name='Bar' limit 5,1

..will not work! I can't find any way to change the last 'Foo' to 'Bar' (but then again I'm quite new to sql). Can it be done? How?

Antheus

2,410

May 07, 2007 10:32 AM

Quote:I can't find any way to change the last 'Foo' to 'Bar' (but then again I'm quite new to sql).

"Last foo", or a "specific foo"?

In general, you aren't guaranteed an order in which results are returned. While "SELECT *" will in general return results in same order, and in the order they were entered, there is no guarantee for that.

This is where indices come into play. You define primary and unique index. Can be auto-incremented, as long as it's unique. Then, when you need to update the elements, you refer to them by that index, not by values.

There is of course a way to do what you want, but I believe that you really absolutely shouldn't do that in order to acomplish what you want.

Best advice I'd give you: Define separate column 'ID', which is primary key and auto-incremental.

"SELECT ID, Name FROM Test;", and each result will be both, id and name.
Find out which of the results you want to change (let's say id=6)
"UPDATE Test SET Name='Bar' WHERE Id='6';"

Wan

1,366

May 07, 2007 10:46 AM

I don't think it's possible to update the last "Foo" without having any kind of unique identifier. In fact, I'm not sure whether there is such a thing as the "last Foo", the order of rows is pretty much undefined. Doesn't it suffice to just update one "Foo"? That would be easy obviously:

UPDATE Test SET Name = 'Bar' WHERE Name='Foo' LIMIT 1

Trying to order the rows first makes no sense either, since the three "Foo" are identical values and will thus keep the same order:

UPDATE Test SET Name = 'Bar' WHERE Name='Foo' ORDER BY Name DESC LIMIT 1

The only solutions I can think of is:

a) Alter the Foo values to something unique, then update the last one, and change the others back to "Foo". You would need some sort of while loop or cursor (if that supported in MySQL)
b) Add a temporary column to serve as a unique identifier, change to last Foo, and remove the column again.
c) Use a temporary table. More "work" than a) or b), so probably the worst option. ;)

But even then.. I'm not sure about MySQL, but in MSSQL adding a primary key to this table in question could potentially change the order of the rows. I'm not quite sure why you would want to update that specific row, but if the order is so important you should establish that when you're selecting the data.

EDIT: beaten. so yeah, pretty much what Antheus already said. :)

656

Author

May 07, 2007 10:52 AM

Quote:"Last foo", or a "specific foo"?

Specific.

Quote:This is where indices come into play.

Ok, I was hoping there way a way to avoid storing a counter for each row.
I feel bad wasting space like that :)

Quote:In general, you aren't guaranteed an order in which results are returned

I hear you, but so far I've never seen any order changes with the current storage engine that I'm using.
It always returns the rows in the order that they were inserted (if no keys are used).

Quote:There is of course a way to do what you want

Tell me, pretty please :)

656

Author

May 07, 2007 11:03 AM

Quote:b) Add a temporary column to serve as a unique identifier, change to last Foo, and remove the column again.

Interseting, haven't though of that I'll try it out.

Quote: I'm not quite sure why you would want to update that specific row

I'm currently implementing a few STL-like containers using a db, set and map is easy since they are key based but vector, list, multiset, multimap and deque is different since they can contain exactly the same data.

Antheus

2,410

May 07, 2007 11:07 AM

Quote:Original post by eq
Quote:"Last foo", or a "specific foo"?

Specific.
Quote:This is where indices come into play.

Ok, I was hoping there way a way to avoid storing a counter for each row.
I feel bad wasting space like that :)

This isn't wasting space, it's using relation (relational database). You cannot have a hash map without a key in the same way you cannot have unique element without unique id.

Quote:
Quote:In general, you aren't guaranteed an order in which results are returned

I hear you, but so far I've never seen any order changes with the current storage engine that I'm using.
It always returns the rows in the order that they were inserted (if no keys are used).
Quote:There is of course a way to do what you want

Tell me, pretty please :)

There *is* a way. But which, depends completely and entirely on actual elements in the table. When the table changes, that will no longer work.

The thing is, you have no way of telling SQL which row to update. When you way "UPDATE ... WHERE X", you cannot define X to be unique. You also have no order among the elements.

SQL is relational database - not sequential container. While it does support certain operations and while objects are always in some sequence, they aren't arrays.

The only way I can see doing this, is by defining a cursor, then iterating through all the elements, and when reaching the last element, changing that.

The syntax for that however depends somewhat on the DB you're using and stored procedures.

But I don't think there's a reliable way to do it, since the predicate "last" cannot be uniquely defined without associated key.

656

Author

May 07, 2007 11:09 AM

Found a solution, to delete the 3:rd element:

ALTER TABLE Test Add Column (TemporaryIndex INT UNSIGNED AUTO_INCREMENT UNIQUE);DELETE FROM Test Where TemporaryIndex=4;ALTER TABLE Test Drop Column TemporaryIndex

Little bit worried about the performance though since the documentation states:

Quote:
In most cases, ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, and then the original table is deleted and the new one is renamed

Antheus

2,410

May 07, 2007 11:14 AM

Quote:I'm currently implementing a few STL-like containers using a db, set and map is easy since they are key based but vector, list, multiset, multimap and deque is different since they can contain exactly the same data.

All of these containers are key based.

The implicit key is the array index that contains pointers to elements. And those elements "waste" space.

List contains a pair( value, next ).

Vector: Implicit ordering - each element is a "column" in SQL. If you resize Vector, you recreate all the columns. Hence, resizing a vector is an expensive operation.

And so on...

Just because you do not see the indexes doesn't mean they aren't there. They are "wasting" just as much space.

656

Author

May 07, 2007 11:14 AM

Quote:There *is* a way. But which, depends completely and entirely on actual elements in the table. When the table changes, that will no longer work.

I guess I'll just have to bite the bullit and store an index behind the scenes.
The only problem I have with that is when you're browsing the database, the usage of my classes will easily hide the index.

Wan

1,366

May 07, 2007 11:15 AM

Quote:Original post by eq
I'm currently implementing a few STL-like containers using a db, set and map is easy since they are key based but vector, list, multiset, multimap and deque is different since they can contain exactly the same data.

But there is nothing to stop you from using a permanent (auto increment) primary key, right?
You may get away by using a clustered index (rows are physically ordered) and/or an unique index on both key and value columns (if key/value pairs are guaranteed to be unique).

Updating a specified row in MySql

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

Updating a specified row in MySql

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

Reticulating splines