Обсуждение: is it possible to do an update with a nested select that references the outer update ?
is it possible to do an update with a nested select that references the outer update ?
От
Jonathan Vanasco
Дата:
A typo in a webapp left ~150 records damaged overnight I was hoping to automate this, but may just use regex to make update statements for this basically , i have this situation: table a ( main record ) id , id_field , fullname table b ( extended profiles ) id_field , last_name , first_name, middle_name , age , etc id_field on table a was left null due to a typo with the orm i've tried many variations to automate it, none seem to work i think this attempt most clearly expresses what I was trying to do UPDATE table_a a set id_field = ( SELECT id_field FROM table_b b WHERE a.first_name || ' ' || b.last_name = a.fullname ) WHERE id_field IS NULL ; I'd be greatful if anyone has a pointer
Re: is it possible to do an update with a nested select that references the outer update ?
От
Alban Hertroys
Дата:
On 6 Oct 2009, at 18:57, Jonathan Vanasco wrote: > i think this attempt most clearly expresses what I was trying to do > > UPDATE table_a a set id_field = ( SELECT id_field FROM table_b b > WHERE a.first_name || ' ' || b.last_name = a.fullname ) WHERE > id_field IS NULL ; > > I'd be greatful if anyone has a pointer You're looking for UPDATE FROM: UPDATE table_a SET id_field = table_b.id_field FROM table_b WHERE table_a.firstname || ' ' || table_b.last_name = table_a.fullname AND table_a.id_field IS NULL; Apparently table_a.id_field is not a primary key? Seems a bit weird to me, but if it floats your boat... Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4acb82e311687224899625!