Error while running SQL server nested UPDATE query

Question

I have tried looking for answer in the forum but couldnt find any which could help me.

My situation is that I have 2 tables, and email in one table has to be updated with respect to ids from another table.

My problem is I am trying to run an Update query, and getting this error:

Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,

, >= or when the subquery is used as an expression.

I realize why I am getting this error, but I am not able to simplify my query. Can any1 help?

UPDATE A
SET    Email = (SELECT B.[Email Address]
                FROM   B
                WHERE  B.ID IN (SELECT ID
                                FROM   A
                                WHERE  Email LIKE 'abc@def.ghi'))
WHERE  A.ID IN (SELECT ID
                FROM   A
                WHERE  Email LIKE 'abc@def.ghi')  

Could anyone suggest me how to proceed?

Answer 1

If I understood correctly, I think you could something like below:

 UPDATE AAlias
 SET
    Email = AAlias.EmailAdd
 FROM
     A AAlias
 INNER JOIN B AAlias
     ON AAlias.ID = BAlias.ID AND A.Email LIKE 'BLAH BLAH'

Hope this helps!!

Answer 2

Not really sure what table structure you have and what exactly do you want to achieve.

If I guess correctly, you could try something like this:

UPDATE A SET Email = (SELECT [Email Address] FROM B WHERE B.ID = A.ID)
WHERE Email = 'abc@def.ghi';

Is this what you wanted (that is, to set A's email field to be equal to the B's [Email Address] from B that matches A's ID)?