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

Question

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.

Answer 1

what about this:

update users set evil=1 where email in (select email from users_evil);

Answer 2

ALTER table add column...

UPDATE users set evil = 0;

UPDATE users u join users_evil ue ON ue.email = u.email and u.name = ue.name set evil = 1;    

you only need to update bad users in users table

Answer 3

You could perform an outer join of the tables, and then detect where a user is evil by looking for non-NULL values:

UPDATE (users LEFT OUTER JOIN evil_users ON users.email = evil_users.email)
  SET users.evil=1
  WHERE evil_users.some_field IS NOT NULL;