Advanced SQLite Update table query -

i trying update table b of database looking this:

table a: id, amount, date, b_id 1,200,6/31/2012,1 2,300,6/31/2012,1 3,400,6/29/2012,2 4,200,6/31/2012,1 5,200,6/31/2012,2 6,200,6/31/2012,1 7,200,6/31/2012,2 8,200,6/31/2012,2  table b: id, b_amount, b_date 1,0,0 2,0,0 3,0,0 

now query data need in 1 select:

select a.*,b.* left join b on b.id=a.b_id a.b_id>0 group b.id  id, amount, date, b_id, id, b_amount, b_date 1,200,6/31/2012,1,1,0,0 3,400,6/29/2012,1,1,0,0 

now, want copy selected column amount b_amount , date b_date

b_amount=amount, b_date=date 

resulting in

id, amount, date, b_id, id, b_amount, b_date 1,200,6/31/2012,1,1,200,6/31/2012 3,400,6/29/2012,1,1,400,6/29/2012 

i've tried coalesce() without success. experienced have solution this?

solution:

thanks answers below, managed come this. not efficient way fine 1 time update. insert first corresponding entry of each group.

replace select id, amount, date  (select  a.id, a.amount, b.id bid inner join b on (b.id=a.b_id) order a.id desc)  group bid; 

so looking seems join inside of update query. in mysql use

update b inner join on b.id=a.b_id set b.amount=a.amount, b.date=a.date; 

but not supported sqlite this related question points out. however, there workaround using replace:

replace b  select b.id, a.amount, a.date  left join b on b.id=a.b_id  a.b_id>0 group b.id; 

the query fill in values of table b columns should keep state , fill in values of table copied values. make sure order of columns in select statement meet column order of table b , columns mentioned or loose these field's data. dangerous future changes on table b. keep in mind change column order/presence of query when changing table b.

something bit off topic, because did not ask that: a.b_id foreign key b.id. seems using value 0 foreign key express there no corresponding entry in b. (inferred select where a.b_id>0.) should consider using null value that. when using inner join instead of left join can drop clause entirely. dbs sort out unsatisfied relations.