I asked a similar question not to long ago, but I've just tried the same sort of query for a similar problem and get an error: "Cannot resolve collation conflict for equal to operation". I've no idea what it means. I've had a search around, but can't see anything that seems to do what I'm after.
All I've got is two tables: 'existing_members' and 'forum_users'. I want to select the forum_users.id (my primary key) where their email address also exists in the existing_members table.
Then, I want to insert the forum_users.id into existing_members.forum_users_id
Are you trying to update a field in table2 with the primary key field from table1 where the email address exists in both table1 and table2 ? Is the email address unique enough to join between the 2 tables ? Please post your update statement.
Right, now I'm getting a "Cannot resolve collation conflict for equal to operation". This would be because the muppets who sent us this (I think their previous data storage policy involved writing stuff on the back of envelopes) have got duplicate entries in there: the same member with a different member number.
So how can I find these duplicate entries? Assuming that the email address is still the best field for comparing individual members, I need to select all entries where the email address occurs elsewhere in the column.
I've tried this, but it seems to be coming up with some weird results:
SELECT email from existing_members WHERE (SELECT COUNT(email) FROM existing_members)>1