CODEWITHSUNDEEP
mysqlselectinsert
Anil
Anil

Reputation: 21910

how do I update table1 values (select from table2) where table1.email = table2.email

I have trying to copy some data from one table to another where the email addresses are the same.

For example:

Table 1 has fields:
email, title, first_name, last_name, (+ others)

Table 2 has fields:
email, title, first_name, last_name, modified, (+ others)

I want to copy the title, first_name, last_name from table2 to table1 where table1.email = table2.email.

So table2 holds all the data, which I would like to copy into table1 where the email in table1 matches the FIRST 'most recent (by 'modified')' email found in table2 (so the most recent modified datetime).

I was trying something like this:

INSERT INTO `table1` (title, first_name, last_name)
    SELECT title, first_name, last_name from `table2`
    WHERE table1.email = table2.email

ERROR: Unknown column 'table1.email' in 'where clause'

So basically, I need something like this in one statement.. 

SELECT EMAIL as `originalEmail` FROM `table1` 
    SELECT title, first_name, last_name FROM `table2` 
    WHERE table2.email = table1.email
    ORDER BY `modified` desc # Get the most recently modified
    LIMIT 1                  # limit to 1 result (the most recent one)
UPDATE `table1` (title, first_name, last_name) values (*values from above*)
WHERE EMAIL = `originalEmail`

Edit - I would like to UPDATE and not INSERT a new record.

Upvotes: 1

Views: 1264

Answers (1)

Devart
Devart

Reputation: 121922

Try this query -

UPDATE `table1` t1
  JOIN `table2` t2
    ON t1.email = t2.email
SET
  t1.title = t2.title,
  t1.first_name = t2.first_name,
  t1.last_name = t2.last_name

Query with most recent condition for table2 records -

UPDATE `table1` t1
  JOIN (SELECT * FROM `table2` GROUP BY email ORDER BY modified DESC) t2
    ON t1.email = t2.email
SET
  t1.title = t2.title,
  t1.first_name = t2.first_name,
  t1.last_name = t2.last_name

Upvotes: 5

Related Questions