INSERT..ON DUPLICATE KEY UPDATE - but NOT using the duplicate key to compare.

Posted by calumbrodie on Stack Overflow See other posts from Stack Overflow or by calumbrodie
Published on 2010-06-07T15:58:01Z Indexed on 2010/06/07 16:12 UTC
Read the original article Hit count: 193

Filed under:
|

I am trying to solve a problem I have inherited with poor treatment of different data sources. I have a user table that contains BOTH good and evil users.

create table `users`( 
  `user_id` int(13) NOT NULL AUTO_INCREMENT , 
  `email` varchar(255) , 
  `name` varchar(255) ,  
  PRIMARY KEY (`user_id`)
);

In this table the primary key is currently set to be user_id.

I have another table ('users_evil') which contains ONLY the evil users (all the users from this table are included in the first table) - the user_id's on this table do NOT correspond to those in the first table.

I want to have all my users in one table, and simply flag which are good and which are evil.

What I want to do is alter the user table and add a column ('evil') which defaults to 0. I then want to dump the data from my 'users_evil') table and then run an INSERT..ON DUPLICATE KEY UPDATE with this data into the first table (setting 'evil'=1 where the emails match)

The problem is that the 'PK' is set to the user_id and not the 'email'. Any suggestions, or even another strategy to successfully achive this.

Can I run this statement but treat another column as PK only for the duration of the statement.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about duplicate-data