Updating table1.field from table2.field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
eyespark
Forum Commoner
Posts: 50
Joined: Tue Jan 24, 2006 7:36 am

Updating table1.field from table2.field

Post by eyespark »

Hi

I have another newbie question for the comunnity. I have 2 tables (let us say table1 and table2) in my database, both have email field. I would like to import emails from table1.email into table2.email but only those that are not already in table2.email. How would php function look like? 8O

Thanks in advance
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

your question is not clear...please use examples..
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

To do in one query this is advanced stuff :)

You can use an insert..select along with a subquery if you're using MySQL4.1 or higher.

Untested:

Code: Select all

insert into
    table2 (email)
select
    email
from
    table1
where
    email not in (select email from table2)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

UNION, the union syntax says it selects as if a distinct of both tables together, so union your tables on the email:

(select `email` from a) UNION (select `email` from b)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Place a unique constraint on the target column and blindfully insert-select from the source column.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

jshpro2 wrote:UNION, the union syntax says it selects as if a distinct of both tables together, so union your tables on the email:

(select `email` from a) UNION (select `email` from b)
this will not work because it will choose distinct entries from table1 and table2 which is not really what he is asking...d11wtq's way is the solution for this.....or you could do timvw's as well....that was a nice idea....
Post Reply