0

I have a table that we've run into an issue because we made a wrong assumption that Table A associates with Table B but it should be Table C. It's only an issue now that we're adding a feature but I'm not sure how to properly migrate it.

Best way I can describe it is say you have an account, vehicle, vehicleHistory, carLot.

Currently vehicleHistory ties to vehicle but really vehicleHistory should be tied to carLot. Example tables. Here's what the table looks like today:

account

id  |
-----
001 |
002 |
003 |

vehicle

id   | accountId
----------------
101  | 001
102  | 002
103  | 003

vehicleHistory

id   | vehicleId
----------------
201  | 101
202  | 102
203  | 103

carLot

id   | accountId
----------------
301  | 001
302  | 002
303  | 003

But it should be like this:

account

id  |
-----
001 |
002 |
003 |

vehicle

id   | accountId
----------------
101  | 001
102  | 002
103  | 003

vehicleHistory

id   | carLotId
---------------
201  | 301
202  | 302
203  | 303

carLot

id   | accountId
----------------
301  | 001
302  | 002
303  | 003

Is this something I should even attempt in PSQL or should I just write a script in the app's language (Node or Scala)?

Oscar Godson
  • 31,662
  • 41
  • 121
  • 201
  • Do you have a way to find out which carlot.id should be referred by each vehicleHistory row? If you don't this can't be done – e4c5 Jan 10 '17 at 00:14
  • yea, it would be vehicleHistory is currently associated with vehicle which associates to account. From account you can get to carLot – Oscar Godson Jan 10 '17 at 00:20
  • sounds good, then this can be done. You don't have an sql fiddle by any chance? if not how about a few more rows for each so that it can be tried out – e4c5 Jan 10 '17 at 00:23
  • @e4c5 added more rows. I do not have a SQL fiddle. – Oscar Godson Jan 10 '17 at 00:35

2 Answers2

1

I would approach this very carefully in the database, but I think it can be done. Basically, you want to replace VehicleHistory with:

select vh.id, cl.carlotid
from VehicleHistory vh left join
     Vehicle v
     on vh.vehicleid = v.id left join
     CarLot cl
     on v.accountid = cl.accountid;

My recommendation would be:

  1. Run the above query and add additional columns.
  2. Check to be very sure that this is what you really want. Check for duplicate rows, mismatches, and whatever else you can think of.
  3. Add a CarLotId to the VehicleHistory table. See if anything breaks.
  4. Populate the new column (see below).

To populate the new column:

update VehicleHistory
    set CarLotId = t.CarLotId
    from <temporary table based on above query> t
    where t.id = VehicleHistory.id;

From what you describe, there should be no reason to remove the original column.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

(unchecked, since I don't have the DDL)

        -- to be safe: make a copy
CREATE TABLE vehicle_history_saved AS select * from vehicle_history;

        -- (maybe drop FK constraints here)
        -- do the update
UPDATE vehicle_history dst
SET vehicle_id = cl.carlot_id
FROM carlot cl
JOIN vehicle ve ON ve.account_id = cl.account_id
WHERE dst.vehicle_id = ve.id
        ;

ALTER TABLE vehicle_history RENAME vehicle_id TO carlot_id;
        -- (maybe recreate FK constraints here)
        -- (maybe drop copy )
-- DROP TABLE vehicle_history_saved;
wildplasser
  • 43,142
  • 8
  • 66
  • 109