CODEWITHSUNDEEP
sqlforeign-keyssql-update
JoelFan
JoelFan

Reputation: 38714

Change foreign key using SQL

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?

Upvotes: 0

Views: 319

Answers (2)

paxdiablo
paxdiablo

Reputation: 881553

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.

Upvotes: 1

Datajam
Datajam

Reputation: 4231

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
);

Upvotes: 1

Related Questions