MySQL: UPDATEing a row with no guaranteed unique field

Question

I am working with an old MySQL table, which serves as a log of sorts. It looks like

CREATE TABLE `queries` (
  `Email` char(32) NOT NULL DEFAULT '',
  `Query` blob,
  `NumRecords` int(5) unsigned DEFAULT NULL,
  `Date` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Now, I need to be able to UPDATE the records in this table (don't ask why, I don't know). Normally, I would just do

UPDATE table SET ... WHERE unique_column = value

But in this case, I don't have a unique column to work from.

Is there a workaround for this, or am I just going to have to push to put in a nice, standard INT NOT NULL AUTO_INCREMENT?

Answer 1

A unique identifier is the only reliable way of doing this. Just add an auto_increment column and be done with it.

For exhaustive info including some workaround approaches (none of them perfect though!) check this question, where the OP had a table without a unique identifier and no way to change it.

Update: As Doug Currie points out, this is not entirely true: A unique ID is not necessary as such here. I still strongly recommend the practice of always using one. If two users decide to update two different rows that are exact duplicates of each other at the exact same time (e.g. by selecting a row in a GUI), there could be collisions because it's not possible to define which row is targeted by which operation. It's a microscopic possibility and in the case at hand probably totally negligeable, but it's not good design.

Answer 2

In case you didn't know this, it will affect performance, but you don't need to use a primary key in your WHERE clause when updating a record. You can single out a row by specifying the existing values:

UPDATE queries
SET Query = 'whatever'
WHERE Email = '[email protected]' AND
  Query = 'whatever' AND
  NumRecords = 42 AND
  Date = '1969-01-01'

If there are duplicate rows, why not update them all, since you can't differentiate anyway?

You just can't do it with a GUI interface in MySQL Query Browser.

If you need to start differentiating the rows, then add an autoincrement integer field, and you'll be able to edit them in MySQL Query Browser too.

Answer 3

There are two different issues here. First, is de-duping the table. That is an entirely different question and solution which might involve adding a auto_increment column. However, if you are not going to de-dup the table, then by definition, two rows with the same data represent the same instance of information and both ought to be updated if they match the filtering criteria. So, either add a unique key, de-dup the table (in which case uniqueness is based on the combination of all columns) or update all matching rows.

Answer 4

UPDATE queries 
SET ... 
WHERE Email = value1 
  AND Query = value2 
  AND NumRecords = value3 
  AND Date = value4 
LIMIT 1;

Answer 5

Delete the duplicates first. What's the point of having duplicate rows in the table (or any table for that matter)?

Once you've deleted the duplicates you can implement the key and they your problem is solved.