0
votes

I am running this query on BigQuery but I'm getting this error: Error: Scalar subquery produced more than one element

I'm just trying to update the Success field from Resources_Usage table to that of the job_events table. I can't really figure out what's wrong in my query.

UPDATE Task_Events.Resources_Usage
SET Success = t2.Success 
FROM Task_Events.Resources_Usage t1 INNER JOIN  Task_Events.job_events 
t2 ON t1.job_id = t2.job_id
WHERE t2.Success is not null
2

2 Answers

2
votes

When you JOIN with job_events - you most likely getting multiple events per job_id in resource_usage - thus multiple t2.Success's
that is what make UPDATE gets confused as it expects just one t2.success

you need to add more condition(s) to produce only one result per row in resources_usage. for example use only last (most recent) event or first event. or event of certain type, etc.

hope this helps

Just as an option you can try GROUP BY to collapse multiple elements to expected one (note: written on go - so just an idea):

UPDATE Task_Events.Resources_Usage
SET Success = MAX(t2.Success) 
FROM Task_Events.Resources_Usage t1 
INNER JOIN Task_Events.job_events t2 
ON t1.job_id = t2.job_id
WHERE t2.Success is not null
GROUP BY t1.job_id
0
votes

I'm not sure why that query would generate that error. I don't see a scalar subquery in the your UPDATE.

However, I think you intend a query more like this:

UPDATE Task_Events.Resources_Usage ru
    SET Success = je.Success 
    FROM Task_Events.job_events je
    WHERE je.job_id = ru.job_id AND je.Success is not null;