INSERT..ON DUPLICATE KEY UPDATE - but NOT using the duplicate key to compare.
- by calumbrodie
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.
