Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: 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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  4. #4
    Join Date
    Jan 2002
    Posts
    189
    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..

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    UPDATE existing_members
    SET forum_users_id=forum_users.id
    FROM existing_members, forum.users
    WHERE existing_members.email=forum_users.email

  6. #6
    Join Date
    Jan 2002
    Posts
    189
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select * from existing_members
    where email in
    (select email
    from existing_members
    group by email
    having count(*) >1 )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •