Change foreign key using SQL

Question

I have a table like this:

ID    Name      Email           Referred_by
-------------------------------------------
1     John      [email protected]    NULL
2     Sam       [email protected]     [email protected]
3     Sally     [email protected]   [email protected]
..... more rows .....

And I would like to change it to:

ID    Name      Email           Referred_by
-------------------------------------------
1     John      [email protected]    NULL
2     Sam       [email protected]     1
3     Sally     [email protected]   2
..... more rows .....

Can I make this change using SQL?

Datajam · Accepted Answer · 2011-05-17 13:09:17Z

1

This should do the trick:

UPDATE my_table a
set    a.referred_by = (
       SELECT b.id
       FROM   my_table b
       WHERE  b.email = a.referred_by
);

answered May 17, 2011 at 13:09

Datajam

4,2612 gold badges25 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

musiKk Over a year ago

I think you have it the other way around.

Datajam Over a year ago

Yeah I fixed it once I realised that :)

HLGEM Over a year ago

And once you do that you should change the datatype to integer so the old values cannot be inserted again.

JoelFan Over a year ago

mysql gives me: You can't specify target table 'a' for update in FROM clause

Datajam Over a year ago

You didn't mention MySQL before - it has trouble with subqueries in UPDATE statements, especially if it's a self join. You need to create a temporary copy of the table (using ENGINE=MEMORY) and self join to that, without aliases (i.e. my_table_temp.email = my_table.referred_by).

paxdiablo · Accepted Answer · 2011-05-17 13:13:06Z

1

Many DBMS' will allow this by the use of DDL (definition) statements rather than DML (manipulation). Assuming that id is an integral type and referred_by is (currently) a textual column, you can do something like:

alter the table to add a new column ref2 (nullable) of the same type as id.
set up a foreign key constraint between that ref2 and id.
populate the ref2 column for all the rows.
drop the original constraint.
alter the table to remove column referred_by.
alter the table to rename column ref2 to referred_by.

answered May 17, 2011 at 13:13

paxdiablo

888k243 gold badges1.6k silver badges2k bronze badges

Collectives™ on Stack Overflow

Change foreign key using SQL

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related