Обсуждение: is it possible to do an update with a nested select that references the outer update ?

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

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!