SQL update multiple table using inner join-CodePudding

I have three tables "batch", "batchyield", "batchsop"

BATCH

|----------|--------------|----------------|-------|
| batch_id | batch_status | actual_produce | stage |
|----------|--------------|----------------|-------|

BATCHYIELD

|--------------|----------------|
|   batch_id   | actual_harvest |
|--------------|----------------|

BATCHSOP

|--------------|----------------|
|   batch_id   | current_status |
|--------------|----------------|

I am trying to update two tables at a time they all are connected with a foreign key I have written a SQL query for that

UPDATE b SET
b.batch_status = 'completed', b.stage = 'flowering', 
b.actual_produce = SUM(byl.actual_harvest), 
bsop.current_status='3' 
from igrow.farm_management_batch b
INNER JOIN igrow.farm_management_batchyield byl ON b.id = byl.batch_id
INNER JOIN igrow.sop_management_batchsopmanagement bsop ON b.id = bsop.batch_id
WHERE end_date < "2022-07-10 00:00:00.000000" and end_date is not null and (batch_status = "running" or batch_status = "to_start")

BUT It says the query is wrong

CodePudding user response：

UPDATE igrow.farm_management_batch b
INNER JOIN ( SELECT batch_id, SUM(actual_harvest) actual_harvest
             FROM igrow.farm_management_batchyield
             GROUP BY batch_id ) byl ON b.id = byl.batch_id
INNER JOIN igrow.sop_management_batchsopmanagement bsop ON b.id = bsop.batch_id

SET b.batch_status = 'completed', 
    b.stage = 'flowering', 
    b.actual_produce = byl.actual_harvest, 
    bsop.current_status='3' 

WHERE end_date < "2022-07-10 00:00:00.000000" 
-- and end_date is not null 
  AND b.batch_status IN ("running", "to_start")

end_date is not null is excess (if previous is true then this is true too), commented.

PS. There is no end_date column in shown tables - where it is taken from?