SQL query to update a table's column from another table-CodePudding

I have 3 tables: SITES, DEVICES AND LOADS.

SITES columns are (ID, default_MGR_ID);
DEVICES columns are (ID, site_id (which is the foreign key of sites))
LOADS columns are (ID, device_id (foreign key of devices), site_mgr_id)

I want to update LOADS.site_mgr_id to be equal to SITES.default_mgr_id.

Can you help me write a SQL query to do that?

This is the query I have so far but is not working:

UPDATE loads SET loads.site_mgr_id =(SELECT default_site_mgr_id FROM sites s WHERE s.id =(SELECT site_id FROM devices d WHERE d.id = loads.device_id)

CodePudding user response：

Using the initial question's field naming notation (You can change to what your field names are); try this:

UPDATE loads SET site_mgr_id = 
(
    SELECT s."default_MGR_ID"
    FROM sites s
    INNER JOIN devices d ON d.site_id = s."ID"
    INNER JOIN loads l ON l.device_id = d."ID"
    WHERE l."ID" = loads."ID"
)

CodePudding user response：

update with subselect, must be guaranteed that returns just one row;

so for a quick answer, you can use one of the following two methods :

update loads
set    loads.site_mgr_id = (
           select top 1 sites.default_mgr_id
           from   devices
                  left outer join sites
                       on  sites.id = devices.site_id
           where  devices.id = loads.device_id
           order by
                  sites.id       desc,
                  devices.id     desc
       )

or use this :

update loads
set    loads.site_mgr_id = sites.default_mgr_id
from   loads
       left outer join devices
            on  devices.id = loads.device_id
       left outer join sites
            on  sites.id = devices.site_id