How to move a column to another table, replacing a FK?

Question

I have 3 tables, one is a parent table to the other two. I want to separate out a column, moving it into the children tables based on the FK. This needs to be accomplished in SQL (no studio shortcuts).

I have 2 tables like these:

TABLE A:
internalID  |  id  | otherdata
============+======+===========
  myid      |   1  | ...
------------+------+-----------
  my2ndid   |   2  | ...
------------+------+-----------
  1234      |   3  | ...
------------+------+-----------
  5678      |   4  | ...

TABLE B:
internalID  |  FK_toTableA  | otherdata
============+===============+===========
  NULL      |       1       | ...
------------+---------------+-----------
  NULL      |       2       | ...

TABLE C:
internalID  |  FK_toTableA  | otherdata
============+===============+===========
  NULL      |       3       | ...
------------+---------------+-----------
  NULL      |       4       | ...

I'd like to move or copy internalID from TABLE A to TABLE B & TABLE C - based on the FK relationship, then delete the internalID column from TABLE A.

The resulting tables should look like this:

TABLE A:
  id  | otherdata
======+===========
   1  | ...
------+-----------
   2  | ...
------+-----------
   3  | ...
------+-----------
   4  | ...

TABLE B:
internalID  |  FK_toTableA  | otherdata
============+===============+===========
  myid      |       1       | ...
------------+---------------+-----------
  my2ndid   |       2       | ...

TABLE C:
internalID  |  FK_toTableA  | otherdata
============+===============+===========
  1234      |       3       | ...
------------+---------------+-----------
  5678      |       4       | ...

SQL version: 13.0.4001.0

This has been asked and answered literally thousands of times. Here is one such example https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql — Sean Lange, Aug 17 '18 at 14:59

Yogesh Sharma · Accepted Answer · 2018-08-17T15:05:17.983

3

You can simple use update statement :

update b
    set b.internalID  = a.internalID 
from b inner join
     a
     on a.id = b.FK_toTableA;

After then you can drop your column internalId from table a.

alter table a
    drop column internalId;

edited Aug 17 '18 at 15:05

answered Aug 17 '18 at 14:59

Yogesh Sharma

49,870
5
26
52

So this runs in a migration task, and I'm getting an error after it has already completed, and try to run it again. I have it wrapped in an IF to prevent this error, dut it doesn't seem to work: `IF EXISTS(...a.internalID...)...` it seems like it's ignoring that IF both in the app and Sever Studio... Any ideas? – Travis Heeter Aug 20 '18 at 13:59
I went ahead and made a post for that, https://stackoverflow.com/questions/51932943/if-does-not-prevent-error-but-only-if-it-has-already-executed – Travis Heeter Aug 20 '18 at 14:28

score 1 · Answer 2 · answered Aug 17 '18 at 15:00

1

Update TableB
set internalId = a.InternalId
from TableA a
where tableB.FK_toTableA = a.Id;

Update TableC
set internalId = a.InternalId
from TableA a
where tableC.FK_toTableA = a.Id;

alter table tableA drop column internalId;

answered Aug 17 '18 at 15:00

Cetin Basoz

22,495
3
31
39

How to move a column to another table, replacing a FK?

2 Answers2

Linked