SQL INNER QUERY returns more than one value in an UPDATE query

inner-query, sql, sql-server, sql-update

Solution

Check this out without using correlated subqueries:

UPDATE  Com
SET     stage_id = Temp4.stage_id
FROM    dbo.component Com
        INNER JOIN 
        ( 
            SELECT Temp2.component_id ,SL.stage_id
            FROM   dbo.stage_link SL
            INNER JOIN (
                            SELECT component_id ,scanner_id
                            FROM   scan
                            WHERE  date_scanned IN (
                                SELECT  MaxScanDate
                                FROM    
                                ( 
                                    SELECT component_id , MAX(date_scanned) MaxScanDate
                                    FROM scan
                                    GROUP BY component_id
                                ) Temp 
                            )
                        ) Temp2 ON Temp2.scanner_id = SL.scanner_id
        ) Temp4 ON Com.component_id = Temp4.component_id

The output:

component_id stage_id
------------ -----------
1            2
2            1
3            1
4            NULL
5            NULL

Problem

I have a table to be updated using records of another table and I am doing this to bring over information from one system (database) to another. The scenario is bit complex, but I desperately need help `:-s` There are 3 tables - `component` , `scan` and `stage_link` component ``` component_id stage_id ------------ -------- 1 NULL 2 NULL 3 NULL 4 NULL 5 NULL ``` scan ``` scan_id component_id scanner_id date_scanned ------- ------------ ---------- ----------------------- 1 1 scanner_a 2012-01-01 07:25:15.125 2 1 scanner_b 2012-01-02 08:14:05.456 3 2 scanner_a 2012-01-01 12:05:45.465 4 3 scanner_a 2012-01-01 19:45:12.536 5 1 scanner_c 2012-01-03 23:33:54.243 6 2 scanner_b 2012-01-02 11:59:12.545 ``` stage_link ``` stage_link_id scanner_id stage_id ------- ---------- ---------- 1 scanner_a 1 2 scanner_b 1 3 scanner_c 2 4 scanner_d 2 5 scanner_e 2 6 scanner_f 3 ``` I need to `update` the table `component` and `set` the field `stage_id` according to the latest scan. Each scan takes the component to a stage according to the scanner involved. I have written the following query in order to `update` the table `component`, but it throws an error saying; `Subquery returned more than 1 value. This is not permitted when the subquery follows '='` The query is; ``` UPDATE component SET stage_id = (select stage_id from( select scn.scanner_id, sl.stage_id from scan scn INNER JOIN stage_link sl ON scn.scanner_id = sl.scanner_id where scn.date_scanned = ( select temp_a.max_date from ( SELECT x.component_id, MAX(x.date_scanned) as max_date FROM scan x where component_id = x.component_id GROUP BY x.component_id ) as temp_a where component_id = temp_a.component_id) ) as temp_b ) ``` I am working on `MS SQL Server` and want to sort this out using no `PHP` or any other language. I have tried for a day to make this work but still didn't get a way to make this work. Any help would be highly appreciated! Thank you very much in advance :-)

Original source