SQL syntax error: no such column

Question

I am new to SQL. I am stuck on the error for a while, I hope some one could help me.

update roads1f set indoorpathway=PathWay1f.Indoor_pathway where astext(roads1f.geometry)= astext(PathWay1f.Geometry)

The error is

no such column PathWay1f.Indoor_pathway

I checked PathWay1f table, it has Indoor_pathway column. Did I use the wrong method to reference data in another table?

PathWay1f and roads1f are almost same. The geometry can be considered as the primary key. PathWay1f has a column Indoor_pathway, but roads1f doesn't have. I just want to copy Indoor_pathway from PathWay1f to roads1f.

Can you provide the definitions of both roads1f and PathWay1f? Are you selecting data from PathWay1f? Where is the portion of your statement that is collecting the data to use for the update? — gmiley
– gmiley, Commented Aug 14, 2017 at 16:56
Actually, PathWay1f and roads1f are almost same. The geometry can be considered as the primary key. PathWay1f has a column Indoor_pathway, but roads1f doesn't have. I just want to copy Indoor_pathway from PathWay1f to roads1f. @gmiley — kevin
– kevin, Commented Aug 14, 2017 at 17:21

ScaisEdge · Accepted Answer · 2017-08-14 17:02:12Z

2

You need the join

update roads1f 
set indoorpathway=( 
        select PathWay1f.Indoor_pathway  
        from PathWay1f
        where astext(roads1f.geometry)= astext(PathWay1f.Geometry))

answered Aug 14, 2017 at 17:02

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kevin Over a year ago

Thanks! It looks like good, but it takes very long time, and haven't finished yet.. My roads1f table has 50,000 rows. Does it take O(n^2)?

ScaisEdge Over a year ago

you have posted a question about an error .. not for performance .. if you have 50.000 the performance depend by several fatcor .. indexes ... hardware .. and others ..and . if you have not the error this mean that my answer should be right then you should rate the asnwer properly ..

Yunnosch Over a year ago

@chen When the code does what it basically is supposed to do (after using scaisEdge proposal) then it is time to either ask a separate specific code question (as scaisEdge implied) with speed aspect here at StackOverflow, or to switch to codereview.stackexchange.com

kevin Over a year ago

@scaisEdge Thanks! My apology.

Collectives™ on Stack Overflow

SQL syntax error: no such column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related