1

I have two tables, one is about the selling, and another table contains the information about the vehicle:

CREATE TABLE vehicle
(
    id                    SERIAL PRIMARY KEY,
    specs_id              INT REFERENCES specs (id) ON DELETE RESTRICT,
    model                 INT REFERENCES model (id) ON DELETE RESTRICT,
    delivery_date         DATE,
    color                 VARCHAR(20),
    plant_id              SERIAL REFERENCES plant (id) ON DELETE RESTRICT,
    date_of_manufacturing DATE
);

CREATE TABLE selling
(
    vehicle      INT REFERENCES vehicle (id) ON DELETE RESTRICT,
    client_id    INT REFERENCES client (id) ON DELETE RESTRICT,
    VIN          CHAR(17) PRIMARY KEY NOT NULL,**
    selling_date DATE,
    subtotal     INTEGER,
    payment      PAYMENT_TYPE,
    seller_id    INT REFERENCES manager (id) ON DELETE RESTRICT
);

I need to move field VIN from selling to vehicle, and I need the existing vehicles to correspond with thier VINs. Here is the table: The tables to modify

I have tried this code: 

ALTER TABLE vehicle
ADD COLUMN VIN CHAR(17);
UPDATE vehicle VIN
SET VIN = selling.VIN
FROM selling

But the result is wrong: all the vehicles get the one VIN from selling:

VEHICLE table after modifying

GMB
  • 216,147
  • 25
  • 84
  • 135
Alex_bgh
  • 13
  • 2
  • Your UPDATE clause needs a WHERE clause to match vehicle_id. Maybe this could be a good example for you to check out how you can write an update like that - https://stackoverflow.com/a/52222942/1066721 – erichste Apr 25 '20 at 16:29

1 Answers1

1

Consider correlating each row in vehicle with the corresponding row in selling, using the foreign key relationship:

UPDATE vehicle
SET vin = s.vin
FROM selling s
WHERE s.vehicle = vehicle.id

As this looks like a 1-N relationship, I would actually suggest:

UPDATE vehicle
SET vin = s.vin
FROM (SELECT DISTINCT vehicle, vin FROM selling) s
WHERE s.vehicle = vehicle.id
GMB
  • 216,147
  • 25
  • 84
  • 135