CODEWITHSUNDEEP
postgresqlinsert
sylvian
sylvian

Reputation: 749

update data in a table from a FK referenced table

I have two tables device_test_results and devices.

device_test_results
- id
- device_id (FK)
- brand
- model
- serial_number

devices
- id
- brand
- model
- serial_number

device_test_results has a foreign key - device_id that references to the devices table

It's been a while since I've used this setup and there's plenty of data that references from device_test_results to devices.

I'm currently trying to get rid of this dependency so I added the brand model and serial_number columns in device_test_results to copy all of that existing information from the devices table and then remove this constraint so that these 2 tables become independent.

Any suggestions or places to look for a solution for this?

Upvotes: 0

Views: 28

Answers (1)

Rahul
Rahul

Reputation: 77896

You should rather try something like below. Get the required data using the SELECT query and JOIN with other table and then perform the INSERT operation accordingly. 

INSERT INTO device_test_results (brand, model, serial_number) 
SELECT * FROM
(
SELECT d.brand, d.model, d.serial_number 
from devices d 
inner join device_test_results dtr on dtr.device_id = d.id
) tab

EDIT:

Then use a UPDATE query doing a JOIN with other table like

UPDATE device_test_results AS dtr 
SET brand = d.brand
 model = d.model
 serial_number = d.serial_number
FROM devices AS d
WHERE dtr.device_id = d.id;

Upvotes: 1

Related Questions