Copy from table1 to table 2 while matching IDs

Discussion in 'MySQL' started by kuttappan, May 23, 2010.

0
  1. #1
    Hello all,

    I have two tables. I have simplified it to:

    table1: ID, fromEmail
    table2: ID, fromEmail (blank Column)

    The ID's of table1 and table2 match, there may be repeated in table2. I want the email fields copied to table2 that have the same ID as table1. I have this so far.

    Of course that won't do it but I don't know how to get the data from table1. Do I use a select statament? I am so thick

    Thank you for any help and direction
     
    kuttappan, May 23, 2010 IP
  2. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #2 
    
UPDATE table2 SET fromEmail=(SELECT fromEmail FROM table1 WHERE table1.ID=table2.ID) WHERE table2.fromEmail IS NULL OR table2.fromEmail='';
    Code (markup):
    but I'm not sure that such denormalization is good.
    Regards :)
     
    koko5, May 24, 2010 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    If you're trying to copy the whole table you can use:

    INSERT INTO table2 SELECT * FROM table1;
    Code (markup):
    If you're just trying to update the email field in table2 an inner join would be the most efficient way to do so.

    UPDATE table2
       INNER JOIN table1
         ON table2.ID = table1.ID 
SET    table2.fromEmail = table1.fromEmail;
    Code (markup):
     
    jestep, May 24, 2010 IP