Grouping Problem

Hello. Struggling to finish this. Getting a group error message.
I have 2 tables.
newtable
id starttime quantity
20 200
30 300
40 600
etc

oldtable
id starttime quantity
20 14:00 100
20 14:00 100
30 15:00 200
30 15:00 100
etc

I want to put the starttime from oldtable into the starttime on newtable where the id matches and the sum of oldtable.quantity matches the oldtable.quantity.

I have this:
update newtable, oldtable set newtable.starttime =
(select starttime from oldtable)
where newtable.id = oldtable.id and SUM(oldtable.quantity) = newtable.quantity

Can anyone please help.

Thanks
Jamie.

 

You should be able to do it with a join.

UPDATE newtable
INNER JOIN (
    oldtable ON newtable.id = oldtable.id
    AND SUM(oldtable.quantity) = newtable.quantity
)
SET newtable.starttime = oldtable.starttime

Code (markup):

 

Thanks Jestep. Unfortunately I'm getting an error with that. Have tried jigging it about but can't work it out. Any ideas?

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON newtable.id = oldtable.id
AND SUM(...........

 

It's sorted now, I managed to work around it. Thanks to those who had a go at it.
Jamie.