MySQL not recognizing field

Question

I have a script that's supposed to update table1's email_id column to match table2's id column wherever the email address in table1 matches an address in table2. It works when I run it on one CSV (with data exported from SalesForce), but not on data in other CSVs exported in the same way one day later.

This is the script:

UPDATE cdata_assignments, email_addresses
  SET cdata_assignments.email_id = email_addresses.id
  WHERE cdata_assignments.email = email_addresses.email_address

I've narrowed the problem down to the cdata_assignments.email field. It looks fine in phpmyadmin (all the entries look like normal email addresses), but scripts don't seem to recognize the entries. Again, this is the same data, exported using the same report template in SalesForce, and it works fine on a CSV exported yesterday, but it won't work on new CSVs today.

The script, and the one Alex suggested below, both work fine on some CSVs. But on others that contain data that should be formatted identically, they don't seem to recognize cdata_assignments.email as a field that can be matched.

Screenshot2 Screenshot3

Possible duplicate of [How to use JOIN in UPDATE query?](http://stackoverflow.com/questions/15209414/how-to-use-join-in-update-query) — Alex, Oct 15 '15 at 15:40
There is no CSV in mysql ;-). Once you import data from csv to mysql, there is mysql table with columns. show me the proof. post some screenshots or text that proof you have data in the table `cdata_assignments` and `email_addresses` on the screenshot keep visisble database name — Alex, Oct 15 '15 at 15:57
My guess is there's a difference in whitespace in the two tables, which is causing the fields to not match. — Barmar, Oct 15 '15 at 16:08
Right, sorry, I meant on data imported from a CSV. I'm a self-taught coding newbie, so apologies if I mix up my terms. I added a screenshot above. cdata_assignments.email is a VARCHAR field; could that be causing a problem? — bgndy, Oct 15 '15 at 16:09
you can't hide values of `email` we should see them in both tables — Alex, Oct 15 '15 at 16:12
Can you add the output of `SHOW CREATE TABLE cdata_assignments` and `SHOW CREATE TABLE email_addresses`? — Barmar, Oct 15 '15 at 17:15
I added those as screenshots; hope I did it right. Does that help? — bgndy, Oct 15 '15 at 17:31

score 0 · Answer 1 · answered Oct 15 '15 at 16:17

It's just a guess, but there may be leading spaces in the values in one of the tables. Try ignoring them when comparing:

UPDATE cdata_assignments AS a
JOIN email_addresses AS e ON TRIM(a.email) = TRIM(e.email_address)
SET a.email_id = e.id

Another possibility is you're using case-sensitive collation on the tables, and they differ in case. Use LOWER(a.email) = LOWER(e.email_address) as the join condition to ignore case. Or change the collation of those columns to case insensitive.

Curses. I was sure that was it when I read this, but it also didn't work. (I tried both TRIM and LOWER, along with a combination of both.) — bgndy, Oct 15 '15 at 17:10

Alex · Answer 2 · 2015-10-15T15:50:35.023

-1

http://sqlfiddle.com/#!9/2297d/1

UPDATE cdata_assignments 
   JOIN email_addresses 
   ON cdata_assignments.email = email_addresses.email_address
   SET cdata_assignments.email_id = email_addresses.id

edited Oct 15 '15 at 15:50

answered Oct 15 '15 at 15:39

Alex

16,739
1
28
51

Didn't work, unfortunately -- though I can see how this is a better script than the one I had been using. – bgndy Oct 15 '15 at 15:46
that works like a charm! http://sqlfiddle.com/#!9/2297d/1 if it not working for you check your data and provide any proof ;-) create your ownsqlfiddle or send some screenshots, data samples. – Alex Oct 15 '15 at 15:50
Yeah, it definitely works script-wise, but my problem seems to be with MySQL not recognizing the cdata_assignments.email field as something it can match, not with the script itself. – bgndy Oct 15 '15 at 15:53
@Alex This is just alternate syntax for the exact same query logic. Why would you expect this query to work any better than the original? – Barmar Oct 15 '15 at 16:11

MySQL not recognizing field

2 Answers2