UPDATE queries SET ... WHERE Email = value1 AND Query = value2 AND NumRecords = value3 AND Date = value4 LIMIT 1;
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?
5条回答 默认 最新
- 已采纳 duanji8615 2010-06-25 21:30点赞 6 评论 复制链接分享
- doumo6356 2010-06-25 21:14
A unique identifier is the only reliable way of doing this. Just add an
auto_incrementcolumn 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.点赞 6 评论 复制链接分享
- duan1443 2010-06-25 21:25
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@example.com' 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.点赞 评论 复制链接分享
- duanban4769 2010-06-25 21:29
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.点赞 评论 复制链接分享
- dongshao4207 2010-06-25 21:50
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.点赞 评论 复制链接分享