0

I'm trying to test how the insert into duplicate key works. And currently this is what I did:

INSERT INTO
user_test (userName, first, last)

SELECT
u.userName, u.first, u.last
FROM otherdatabase.user as u

ORDER BY u.userName ASC

ON DUPLICATE KEY UPDATE
userName = u.userName

I executed this query and it worked and inserted rows. Then what I did was I modified one row from the user table and then tried to run this query again, but instead of just updating that one row it inserted all the rows again.

From what I understand shouldn't it just update that one row I modified from the user table?

What I'm trying to do is do a "Insert if it doesn't exist and update if it exist" query and found that using insert into .. on duplicate key can do that but I'm obviously doing it wrong... 

CREATE TABLE user_test (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  userName varchar(20) DEFAULT NULL,
  first varchar(20) DEFAULT NULL,
  last varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=latin1

Per Barranka's suggestion I added a unique index to the user_name column

Alter table user_test add unique index idx_userName(userName)

Then I ran the query again and it didn't add any more rows since its already existing.. This is an example of what the user_table looks like now, its now the same on the users table.

user_table:

userName    |   first   |   last    |
ckeith      |   Carl    |   Keith   |
mmonroe     |   Mickey  |   Monroe  |

Then what I did to test it again is from the user table I modified one of the rows

user:

userName    |   first   |   last    |
ckeithh     |   Carl    |   Keith   |
mmonroe     |   Mickey  |   Monroe  |

and executed the query again, this is now what the users_table looks like:

user_table:

userName    |   first   |   last    |
ckeith      |   Carl    |   Keith   |
mmonroe     |   Mickey  |   Monroe  |
ckeithh     |   Carl    |   Keith   |

I thought it would just update the first row to ckeithh but it just inserted one row? My expected output was:

user_table:

userName    |   first   |   last    |
ckeithh     |   Carl    |   Keith   |
mmonroe     |   Mickey  |   Monroe  |

Update:

I added a unique index and made sure that it is unique. The inserting works but now the update is not working. Anything else i should try?

Still not able to get this to work, I have confirmed that the column i'm using is unique and that my version of mysql is v5 (i saw on one of the forums that for this to work mysql should be v5, not sure if thats real or not, but still i checked and im using v5.5.37)

hocuspocus31
  • 183
  • 1
  • 10
  • 1
    What does the `SHOW CREATE TABLE user_test` look like? The `ON DUPLICATE KEY UPDATE` is only of use if the column in question has a `UNIQUE` index or primary key. – Michael Berkowski Dec 01 '14 at 21:40
  • do you actually have a unique key on your `user_test` table? on duplicate key requires one for the "update" portion to work. no duplicate key violation, no update. – Marc B Dec 01 '14 at 21:40
  • @MichaelBerkowski this is the output of the show create table user_test ... CREATE TABLE `user_test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `userName` varchar(20) DEFAULT NULL, `first` varchar(20) DEFAULT NULL, `last` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=latin1 – hocuspocus31 Dec 01 '14 at 21:46
  • @MarcB yes i do, the id and userName columns is unique and i use the id column as primary key – hocuspocus31 Dec 01 '14 at 21:47
  • 1
    @hocuspocus31 But in the `CREATE TABLE` you posted, there is no `UNIQUE` index on `userName`. You would need `UNIQUE INDEX (userName)` defined in there. – Michael Berkowski Dec 01 '14 at 21:49
  • there is no unique username key in your table dump. You have only a primary key defined, and since you're **NOT** using that id in your insert/select, it's considerd a null value by mysql and will simply create a new record with a new ID. – Marc B Dec 01 '14 at 21:49
  • 1
    @hocuspocus31 That's an option: Set `user_name` as primary key. Or, another way, add a unique index to `user_name`: `alter table user_test add unique index idx_user_name(user_name)` – Barranka Dec 01 '14 at 21:54
  • @MichaelBerkowski where should I add the UNIQUE INDEX (userName)? inside the select query? or on the ON Duplicate...? – hocuspocus31 Dec 01 '14 at 21:54
  • @hocuspocus31 http://dev.mysql.com/doc/refman/5.0/en/create-index.html – Gerardo Charles Rojas Vega Dec 01 '14 at 22:01
  • @Barranka ok i did that and it was an improvement, i executed the query and it inserted the rows and then i tried to execute the same query again and it didn't insert any rows so its right. and then i tried editing one row from the user table... i thought it would update the row i already have on user_table but it just inserted a new row with the modified username.. shouldn't it just update the row i already have? – hocuspocus31 Dec 01 '14 at 22:02
  • @Barranka i updated the details on my question incase what i just said above is confusing.. – hocuspocus31 Dec 01 '14 at 22:10

0 Answers0