MySQL duplication

Question

Using SQLyog, I was testing whether the correct value was set into table. And I tried

SELECT type_service FROM service WHERE email='[email protected]'

So, only one result was output.

type_service
0 

To continue to test, I tried to set value, 1 by force which gave the warning

Warning

There are 2 duplicates of the row you are trying to update. Do you want to update all the duplicates?

Note: You can turn off this warning by unchecking Tools -> Preferences -> Others -> Prompt if multiple rows are getting updated.

But I thought I already placed limitations with where clause. So I pushed yes. As a result, the value of all the data in type_service column was changed to 1. Why?

Answer 1

You have 2 exact duplicate rows in table. Exact. It is a friendly warning, but most likely needs to be addressed by a slight schema change.

The most simple solution is to alter the table and add an auto_increment Primary Key column.

Mysql Alter Table Manual page here.

See this Webyog FAQ link.

Whenever I am about to spook up another table, I usually stub it out like:

create table blah
(
    id int auto_increment primary key,
    ...
    ...
    ...
);

for safety sake.

Were you not to have the auto_increment PK, see the following.

create table people
(
    firstName varchar(40) not null,
    lastName varchar(40) not null,
    age int not null
);

insert people (firstName,lastName,age) values ('Kim','Billings',30),('Kim','Billings',30),('Kim','Billings',30);

select * from people;

-- this could be bad:
update people
set age=40
where firstName='Kim' and lastName='Billings';

ALTER TABLE people ADD id INT PRIMARY KEY AUTO_INCREMENT;

select * from people;  -- much nicer now, schema has an id column starting at 1

-- you now at least have a way to uniquely identify a row