sql – Need a query to move all the emails from one table into another using a common row value as the link – Education Career Blog

An import was performed on my database which wasn’t done so well. It’s resulted in some fields being empty where they shoudln’t, etc…

What I need to do now is move all the data from one column in one table into a column in a different table. Both tables have an ID which is a link to each rows.

For example:

Table1

id | linkID | email
---+--------+-------------------
1  |  7     | 
---+--------+-------------------
2  |  3     | [email protected]

Table2

id | email
---+-----------------
7  | [email protected]
---+-----------------
3  | [email protected]

I was going to write a small PHP script to pull out all the rows from Table2 and UPDATE Table1 using the id and email. But I was wondering if this could be done entirely through SQL queries?

,

update table1 set email = (select email from table2 where table1.linkID=table2.id);

,

update table1
inner join table2 on table1.linkID = table2.id
set table1.email=table2.email;

if you only want to set the empty emails:

update table1
inner join table2 on table1.linkID = table2.id
set table1.email=table2.email
where table1.email = '';

,

update table2 set email = (select email from table1 where table1.linkId = table2.id)

Leave a Comment