title: Insert Into.. Select.. On Duplicate Key Update
body: <p>I'm trying to test how the insert into duplicate key works. And currently this is what I did:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>From what I understand shouldn't it just update that one row I modified from the user table?</p>
  
  <p>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... </p>
  
  <pre><code>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 
  </code></pre>
  
  <p>Per Barranka's suggestion I added a unique index to the user_name column</p>
  
  <pre><code>Alter table user_test add unique index idx_userName(userName)
  </code></pre>
  
  <p>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.</p>
  
  <p>user_table:</p>
  
  <pre><code>userName    |   first   |   last    |
  ckeith      |   Carl    |   Keith   |
  mmonroe     |   Mickey  |   Monroe  |
  </code></pre>
  
  <p>Then what I did to test it again is from the user table I modified one of the rows</p>
  
  <p>user:</p>
  
  <pre><code>userName    |   first   |   last    |
  ckeithh     |   Carl    |   Keith   |
  mmonroe     |   Mickey  |   Monroe  |
  </code></pre>
  
  <p>and executed the query again, this is now what the users_table looks like:</p>
  
  <p>user_table:</p>
  
  <pre><code>userName    |   first   |   last    |
  ckeith      |   Carl    |   Keith   |
  mmonroe     |   Mickey  |   Monroe  |
  ckeithh     |   Carl    |   Keith   |
  </code></pre>
  
  <p>I thought it would just update the first row to ckeithh but it just inserted one row?
  My expected output was:</p>
  
  <p>user_table:</p>
  
  <pre><code>userName    |   first   |   last    |
  ckeithh     |   Carl    |   Keith   |
  mmonroe     |   Mickey  |   Monroe  |
  </code></pre>
  
  <hr>
  
  <p>Update: </p>
  
  <p>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?</p>
  
  <hr>
  
  <p>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)</p>