Thread: Mass Update

Hi,

This is probably a really easy question but I have been toiling with
this SQL for ages.
Okay I have a table with geom_id and id in, this table is called temp.
I also have another table called junctions with a column, old_id. Now
old_id corresponds to geom_id. I want to update the junctions table
column id with the id held in temp where the geom_id column matches
the old_id column.

I would really appreciate some help on this. I just cant seem to get
it right.

Thanks

Iain

On Apr 23, 4:25 am, Iain Adams <aca04...@shef.ac.uk> wrote:
> Hi,
>
> This is probably a really easy question but I have been toiling with
> this SQL for ages.
> Okay I have a table with geom_id and id in, this table is called temp.
> I also have another table called junctions with a column, old_id. Now
> old_id corresponds to geom_id. I want to update the junctions table
> column id with the id held in temp where the geom_id column matches
> the old_id column.
>
> I would really appreciate some help on this. I just cant seem to get
> it right.
>
> Thanks
>
> Iain


update junctions set id = (select id from temp where temp.geom_id =
junctions.old_id AND temp.id <> junctions.id);

This assumes that their is no more then 1 cooresponding geom_id for
each old_id.

Roger

Roger,

This statement runs but the table isnt actually updated. Slightly odd.
I ran the subquery, which makes perfect sense and nothing was
returned. If I remove the AND temp.id <> junctions.id part I get loads
of results returned but if I run the update with the the adjusted
subquery I get ERROR:  more than one row returned by a subquery used
as an expression. Dont quite know what is going wrong.

Thanks alot

Iain