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='test@gmail.com'

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?

in addition to what @HolmesIV said, the gui tools are warning you that you may be about to modify more rows than you meant to. They each have ways of suppressing the warning (yog, mysql workbench, etc). A sort of once and for all setting, basically "I am a veteran programmer, leave me alone" setting — Drew, Aug 06 '15 at 23:58
What does your table look like, and what data is in your table, as reported by `SELECT * FROM SERVICE`? — Bob Jarvis - Слава Україні, Aug 06 '15 at 23:59

Drew · Answer 1 · 2015-08-07T00:27:57.693

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

MySQL duplication

1 Answers1