CODEWITHSUNDEEP
sqlmysql
jerrygarciuh
jerrygarciuh

Reputation: 22018

No Changed Rows Produced by this mySQL update query. Why?

I am at a loss this morning. Maybe my coffee was drugged? Simple problem- get the existing ids into this temp table for an export.

Tables like so:

Table person

+--------+-----------------------+
| id     | email                 |
+--------+-----------------------+
| 142755 | [email protected] |
+--------+-----------------------+

Table no_dma

+--------+------------------------+
| person | email                  |
+--------+------------------------+
|     0  | [email protected]  |
+--------+------------------------+

Query: 

UPDATE 
person, no_dma
SET no_dma.person = person.id
WHERE person.email = no_dma.email;

I have verified the existence of at least some matching email addresses in the two tables but the update produces

Query OK, 0 rows affected (9.31 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Clearly I have a little dain bramamge today.

Help me out? What am I doing incorrectly?

// EDIT

Per comments below I made these queries:

mysql> select person, email from no_dma limit 0,1;
+--------+------------------------+
| person | email                  |
+--------+------------------------+
 |     0 | [email protected]
+--------+------------------------+
1 row in set (0.00 sec)

mysql> select email from no_dma where email = '[email protected]';
Empty set (0.00 sec)

mysql> select email from no_dma where TRIM(email) = '[email protected]';
Empty set (0.46 sec)

Both tables have email field stored as varchar with collation set to latin1_swedish_ci.

And this this query, WTH?

mysql> SELECT CONCAT('"',email,'"') from no_dma limit 0,3;
+-----------------------+
| CONCAT('"',email,'"') |
+-----------------------+
"  |[email protected]
"   |[email protected]
"  |[email protected]
+-----------------------+


mysql> SELECT email from no_dma limit 0,3;
+--------------------+
| email              |
+--------------------+
 |[email protected]
  |[email protected]
 |[email protected]
+--------------------+

What is going on there? Looks like newlines but I thought TRIM() handled those?

mysql> SELECT TRIM(email) from no_dma limit 0,3;
+--------------------+
| TRIM(email)        |
+--------------------+
 |[email protected]
  |[email protected]
 |[email protected]
+--------------------+
3 rows in set (0.00 sec)

UPDATE: FOUND ISSUE import was done on a Windows generated CSV but mysqlimport was given arg

--lines-terminated-by='\n'

Reimported data works fine. Sorry to have wasted folks time.

Upvotes: 1

Views: 533

Answers (2)

gbn
gbn

Reputation: 432521

Table no_dma has a trailing space. The data is not the same.

Edit:

  • SET ANSI_PADDING?
  • Is it really a space:   is ASCII 160
  • What does a hash or checksum of each value reveal?
  • What are the string lengths?

Upvotes: 3

Raffael
Raffael

Reputation: 20045

The statement is fine, I think. B/c I tested it and it worked.

Upvotes: 1

Related Questions