comparing tables... again...

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

Does that make sense?no, that doesn't make sense

you want to select forum_users ids where the email already exists in existing_members, and then insert them? won't they be duplicates?

it might help to see your query sql, too

rudy|||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.|||OK, here's what I tried:

UPDATE existing_members SET forum_users_id=forum_users.id WHERE email=forum_users.email

but obviously that's not gonna work. I need to kinda do a join on the tables but I'm not sure how?

Cheers..|||UPDATE existing_members
SET forum_users_id=forum_users.id
FROM existing_members, forum.users
WHERE existing_members.email=forum_users.email|||Aha - I didn't know you could do that :)

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|||select * from existing_members
where email in
(select email
from existing_members
group by email
having count(*) >1 )sqlsql