Обсуждение: 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