CODEWITHSUNDEEP
sqlpostgresql
Elijah Leis
Elijah Leis

Reputation: 415

More than one row returned by a subquery and help on logic formulating

I have 3 tables: event table, down_event table, dummy table

In the event table it contains the column terminal_id,event_start_adj,event_end_adj and event_duration_sec see picture below: event table

Now in the down_event table, it contains the column id, terminal_id, event_description, down_date, down_time, down_duration_sec. See picture below:

down_event table

Lastly, the 3rd table is the dummy table which contains columns: terminal_id, availability_date, availability_time, duration_sec. See picture below:

dummy table

In the dummy table the duration_sec has a default value of 3600 while the availability_time intervals from 00:00:00 up to 23:00:00. The availability_date is the one that sorts all the date when the event happened in the down_event table.

The problem is i need to subtract the down_duration_sec from the default value of the duration_sec of the dummy table and update it. So if the down_duration_sec for example is 1 second then it will be 3600 - 1 = 3599. Then if that event occurred at 03:04:44 (found in the event table and down_event table) it will fall on the 03:00:00 on the dummy table. Then the event_duration_mins that is on the event table that has a value of 42 will be added on the 03:00:00 updating it in the dummy table to 03:42:00 and the duration_sec to 3599 for the date of 2020-04-01.

First I have tried updating the duration_sec with my query below:

UPDATE dashboard.dummy SET duration_sec = 3600 - (SELECT down_duration_sec FROM dashboard.down_event WHERE terminal_id IN (SELECT terminal_id FROM dashboard.dummy)) WHERE terminal_id IN (SELECT terminal_id FROM dashboard.down_event WHERE terminal_id IN (SELECT terminal_id FROM dashboard.dummy)) AND availability_date IN (SELECT down_date FROM dashboard.down_event WHERE down_date IN (SELECT availability_date FROM dashboard.dummy))

but the problem PostgreSQL threw an error of ERROR: more than one row returned by a subquery used as an expression. I need help on my query and what to do for the other columns in my dummy table that's why I explained everything. I'm planning to update it one by one but i think it will take a lot of time doing it. So if there is any idea that i can try i'll try it but for now i don't know why PostgreSQL is throwing that error to me. I it's because of the = sign in the duration_sec = but i've tried doing it as an IN but still won't work.

Edit:

Each terminal_id has different down_duration_sec on the down_event_table.

Please see screenshot below: duration-sec

Upvotes: 1

Views: 120

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

Your subquery return more then a row so you could try using limit 1 

UPDATE dashboard.dummy 
SET duration_sec = 3600 - (
    SELECT down_duration_sec 
    FROM dashboard.down_event 
    WHERE terminal_id IN (SELECT terminal_id FROM dashboard.dummy)
    LIMIT 1
) 
WHERE terminal_id IN (  
    SELECT terminal_id 
    FROM dashboard.down_event 
    WHERE terminal_id IN (SELECT terminal_id FROM dashboard.dummy)
) 
AND availability_date IN (
    SELECT down_date 
    FROM dashboard.down_event 
    WHERE down_date IN ( SELECT availability_date FROM dashboard.dummy)
)

OR use an aggreation function eg: min() or max() o avg()

UPDATE dashboard.dummy 
SET duration_sec = 3600 - (
    SELECT min(down_duration_sec) 
    FROM dashboard.down_event 
    WHERE terminal_id IN (SELECT terminal_id FROM dashboard.dummy)
) 
WHERE terminal_id IN (  
    SELECT terminal_id 
    FROM dashboard.down_event 
    WHERE terminal_id IN (SELECT terminal_id FROM dashboard.dummy)
) 
AND availability_date IN (
    SELECT down_date 
    FROM dashboard.down_event 
    WHERE down_date IN ( SELECT availability_date FROM dashboard.dummy)
)

If you need a value for each terminal then you need a group by for terminal and join the subquery 

UPDATE dashboard.dummy 
SET duration_sec = 3600 - t.my_duration
FROM (  
    SELECT terminal_id , min(down_duration_sec)  my_duration
    FROM dashboard.down_event 
    WHERE terminal_id IN (SELECT terminal_id FROM dashboard.dummy)
    GROUP BY terminal_id
) t 
WHERE t.terminal_id = dashboard.dummy.terminal_id
AND availability_date IN (
    SELECT down_date 
    FROM dashboard.down_event 
    WHERE down_date IN ( SELECT availability_date FROM dashboard.dummy)
)

and for distinct values you should use DISTINCT clause 

     UPDATE dashboard.dummy 
     SET duration_sec = 3600 - t.down_duration_sec 
     FROM ( 
        SELECT DISTINCT down_duration_sec, down_date, terminal_id 
        FROM dashboard.down_event 
        WHERE terminal_id IN (SELECT terminal_id FROM dashboard.dummy) 
     ) t 
     WHERE t.terminal_id = dashboard.dummy.terminal_id 
     AND dashboard.dummy.availability_date = t.down_date

Upvotes: 1

Related Questions