sql - Update newly added column according to foreign key in another column -

how can i, after altering table filled data, update new column values according existing foreign key id.

example:

let's have table carsales, group sales quantities either individual cars or car type:

id | carid | cartype  | quantity | saledate 1  | 1     | null     | 2        | <some date> 2  | 2     | null     | 3        | <some date> 3  | 2     | null     | 1        | <some date> 4  | null  | 'trucks' | 12       | <some date> ... 

carid foreign key connect table cars:

carid | color  | pistonmotion 1     | 'blue' | v 2     | 'gray' | v 3     | 'pink' | linear 4     | 'blue' | linear ... 

then decide need group sales quantities piston motion, add new column pistonmotion carsales:

id | carid | cartype  | pistonmotion | quantity | saledate 1  | 1     | null     | null         | 2        | <some date> 2  | 2     | null     | null         | 3        | <some date> 3  | 2     | null     | null         | 1        | <some date> 4  | null  | 'trucks' | null         | 12       | <some date> ... 

now, carsales full of data (let's have thousands of rows), need update pistonmotion each row in table according carid on same row one-time action. want group both carid , cartype along pistonmotion in carsales table don't want join query cars table in future. know not able update pistonmotion on rows carid null, doesn't matter, since these not actual tables, please disregard that.

the question simply: query need in order update pistonmotion in carsales equal pistonmotion in cars same carid shared?

you need update inner join:

update cs set pistonmotion = c.pistonmotion carsales cs inner join cars c on c.carid=cs.carid