Subquery returns more than 1 row

Hi,

I'm trying to update one table based on a matching column from another table.

Here is the query:

UPDATE dbname.llo_studentsdemo SET myNewID=(SELECT NewUserName FROM dbname.ecEveryoneElse WHERE UserName=id);

Here's the error:
error : Subquery returns more than 1 row

I need to do this for all 133000 records in the llo_studentsdemo table. There are several records with duplicate id values in this (llo_studentsdemo) table.


Table 1: (ecEveryoneElse) (This table contains the new data, 98000 records. These are the only two columns in the table.)
+---------------+---------------+
| UserName | NewUserName |
+---------------+---------------+
| 3282384 | 2wsUShrwj45h |
| 2293848 | 38Ssjdjr423s |
| 2047765 | 2OSIv0s4i348 |
+---------------+---------------+

Table 2: (llo_studentsdemo) (This is the table to be updated, 133000 records. There are many other columns in addition to id and myNewID. If there is no match then leave myNewID as null.)

+---------------+---------------+---...
| id | myNewID |
+---------------+---------------+---...
| 3282384 | <null> (value is 2wsUShrwj45h after query runs)
| 2293848 | <null> (value is 2wsUShrwj45h after query runs)
| 2047765 | <null> (value is 2wsUShrwj45h after query runs)
+---------------+---------------+---...

I ran this query successfully with 100 and 3500 records, but when I try it with 98000 records, I get the error, "error : Subquery returns more than 1 row".

Any ideas?

Mike

 

This (SELECT NewUserName FROM dbname.ecEveryoneElse WHERE UserName=id) is returning more than 1 row. You can't update 1 row with more than 1 row. Your UserName column is not unique?

 

Use this query to figure what the duplicate user names are:

SELECT UserName, Count(*) FROM dbname.ecEveryoneElse GROUP BY UserName HAVING Count(*) > 1

PHP: