MySQL Unique Row Identifier

If I want to UPDATE a record in a MySQL database which doesn't have a unique auto increment key or id in the first column, the only way I can update the row is based on non unique information. So if I do an update on a name i'd run the risk of overwriting all other instances.

Is there any way of selecting a unique row id that I can update against? Without having to alter the database structure and adding another column for a unique id?

 

There are functions such as rowid, rownum in other SQL databases but not MySQL.

The above will assign a row id to each row in a select statement, which I suppose could be used as a more reliable identifier but it's still open to problems if the rows were to change or inserted.

Any suggestions?

 

If there's no unique identifier, how do you know its unique? If you can't verbally define how your data is unique you have no hope of having a database doing so.


'John', 'Smith', '1234 Main Street', 'Plano', 'TX'
'John', 'Smith', '1234 Main Street', 'Plano', 'TX'
'John', 'Smith', '1234 Main Street', 'Plano', 'TX'

Without using their position in relation to each other as I just listed them (i.e. middle one, 1st one), tell me how each one is unique. You can't--the data is not unique and in an update query they will all get updated.

 

You need to create an auto increment column to work on. If your datra is not unique then every time you do a select, the rows are not guaranteed to be returned in the same sequence.

You definitely need a unique column to work on.

 

Is there an insert datetime or timestamp field? You could use that as a unique key if it's in the database. Otherwise you need to alter the table and add an autoincrement column.

 

Whenever there is no unique identifier in MySql, combining all fields makes identifier for one row, however, there can be duplicate rows as well, but if you look at it logically, what is the use of having duplicate rows and updating only one of them in such cases? NO Use I would say. SO ultimate way to update a row in such cases is to specify each and every field in table for update query. BUT if you want peace of mind, add an auto incremented column to table.

 

Unfortunatley I don't have the option of inserting an auto increment field.

What I've done is check using _rowid if a primary key exists in the table i am updating, if so update using this. Otherwise specify every field for update query and LIMIT 1.

Thanks v much for the replies.