Update field with identically named field from another table

[sailor152]

I am having two tables, table A has the following fields:
cust_ID, email
Table B has the following fields:
ID, email, name
I want to move email from Table A to B when the cust_ID = ID.
Please notice that both tables are filled with the same ids..

Anyone can help with my sql statment for this

[ronverdonk]

Welcome to The Scripts![code=mysql]update B set email=(select email from A where A.Cust_id=B.Id) ;[/code]That will do it.

Ronald

[ronverdonk]

Please remember to provide a meaningful Title for any threads started (see the FAQ entry Use a Good Thread Title).

This helps to ensure that other members, and also the general public, will have a better chance of finding answers to any similar questions.

MODERATOR

[sailor152]

Welcome to The Scripts![code=mysql]update B set email=(select email from A where A.Cust_id=B.Id) ;[/code]That will do it.

Ronald

Having this error
--------------------------------------------------------------------------------
Error

SQL query: Documentation

UPDATE B SET email = ( SELECT email
FROM A
WHERE A.Cust_id = B.ID )

MySQL said: Documentation
#1242 - Subquery returns more than 1 row
---------------------------------------------------------------------------------

[ronverdonk]

So table A has duplicates, yes?

Ronald

[sailor152]

So table A has duplicates, yes?

Ronald

Table A have the ids stored.. I want to add the emails to those ids when it matches the table B ids

[amitpatel66]

Table A have the ids stored.. I want to add the emails to those ids when it matches the table B ids

Your table A contains more than one record for particular ID because of which it is throwing that error. So with which EMAIL ID you need to perform an UPDATE?? You can use AGGREGRATE function if that satisfies your requirement else try with EXISTS clause.

[ronverdonk]

Table A have the ids stored.. I want to add the emails to those ids when it matches the table B ids

That I know. But what when your table B has more rows with an identical ID, which one must then be inserted into A??

Ronald