0

I have a membership-table with userid as primary key.

I have another table (table A) containing two foreign keys to the membership-table (tableA.userid1 and tableA.userid2).

Now I want to update the emailadress inside the membership-table of userid2 with the emailadress of userid1.

How can I do that? Something like...

Update membership m2
set m2.email = m1.email
...???
Leon
  • 65
  • 5

2 Answers2

2
UPDATE m2
SET email=m1.email
FROM TableA ta
INNER JOIN membership m1 ON ta.UserID1=m1.UserID
INNER JOIN membership m2 ON ta.UserID2=m2.UserID
UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51
1
 update A
     join membership as m1 on A.userid1 = m1.id
     join membership as m2 on A.userid2 = m2.id
 set m2.email = m1.email

where

create table membership (
  id int primary key auto_increment,
  email varchar(100)
);

create table A (
  userId1 int,
  userId2 int,
  constraint foreign key (userId1) references membership (id),
  constraint foreign key (userId2) references membership (id)
);

insert into membership (email) values
  ("user1@stackoverflow.com"),
  ("user2@stackoverflow.com");

insert into A values (1, 2);

The update command sets the email of user 2 to that of user 1:

select * from membership;

id  email
1   user1@stackoverflow.com
2   user1@stackoverflow.com

Here is a link to SQL Fiddle

Micha Wiedenmann
  • 19,979
  • 21
  • 92
  • 137