Unknown column error accessing outer query column inside subquery

Question

I don't know how to explain my problem better than just showing the code and the error.
The following query is a minimal example for the problem I have encountered

UPDATE stops AS stops1 SET real_stop_id = (
    SELECT MIN(served_by2.stop_id)
    FROM served_by AS served_by1 
    INNER JOIN served_by AS served_by2
    USING(route_short_name)
    WHERE served_by1.stop_id = stops.stop_id
);

It returns the following error.

ERROR 1054 (42S22): Unknown column 'stops.stop_id' in 'where clause'

I'm using the timetable data for my city's tram network in the Google Transit Feed Specification format
https://developers.google.com/transit/gtfs/reference

In addition to this I have created and populated a table served_by.

mysql> describe served_by;
+------------------+---------+------+-----+---------+-------+
| Field            | Type    | Null | Key | Default | Extra |
+------------------+---------+------+-----+---------+-------+
| stop_id          | int(11) | YES  |     | NULL    |       |
| route_short_name | text    | YES  |     | NULL    |       |
+------------------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from served_by limit 5;
+---------+------------------+
| stop_id | route_short_name |
+---------+------------------+
|     378 | 19               |
|     378 | 19               |
|     378 | 19               |
|     378 | 19               |
|     398 | 5                |
+---------+------------------+
5 rows in set (0.06 sec)

For completeness, here is the stops table, which follows the GTFS format except for the column real_stop_id (which has no data), that I am currently trying to populate.

mysql> describe stops;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| stop_id      | int(11)      | YES  | MUL | NULL    |                |
| real_stop_id | int(11)      | YES  |     | NULL    |                |
| stop_no      | varchar(30)  | YES  | MUL | NULL    |                |
| stop_name    | varchar(150) | YES  | MUL | NULL    |                |
| stop_lat     | double       | YES  |     | NULL    |                |
| stop_lon     | double       | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

mysql> select * from stops limit 5;
+----+---------+--------------+---------+--------------------------------------------+-------------------+------------------+
| id | stop_id | real_stop_id | stop_no | stop_name                                  | stop_lat          | stop_lon         |
+----+---------+--------------+---------+--------------------------------------------+-------------------+------------------+
|  1 |   10311 |          947 | 45      | 45-Glenferrie Rd/Wattletree Rd (Malvern)   |  -37.862296736384 | 145.028194441473 |
|  2 |   10371 |          946 | 44      | 44-Duncraig Ave/Wattletree Rd (Armadale)   | -37.8618932396197 | 145.025090664641 |
|  3 |    1083 |         1083 | 42      | 42-Clyde St/Raleigh Rd (Maribyrnong)       | -37.7696986370071 | 144.898841257316 |
|  4 |   11285 |          940 | 43      | 43-Egerton Rd/Wattletree Rd (Armadale)     | -37.8615917660895 |  145.02270030676 |
|  5 |    1185 |         1185 | 50      | 50-Vincent St/Wattletree Rd (Malvern East) | -37.8643850684538 |  145.04371198053 |
+----+---------+--------------+---------+--------------------------------------------+-------------------+------------------+
5 rows in set (0.06 sec)

Gordon Linoff · Accepted Answer · 2020-04-10 10:25:56Z

1

You have renamed it to stops1. You need to use the alias instead of the original table name:

UPDATE stops s
     SET real_stop_id = (SELECT MIN(sb2.stop_id)
                         FROM served_by sb1 JOIN
                              served_by sb2
                              USING (route_short_name)
                         WHERE sb1.stop_id = s.stop_id
                        );

I find that table abbreviations are easier to write and read.

edited Apr 10, 2020 at 10:25

answered Apr 10, 2020 at 10:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Issa Chanzi Over a year ago

Thank you. I didn't know what the convention for table aliases was. Many people encourage using fully descriptive names in code over abbreviations for clarity, and I wasn't sure if the same principle applied to SQL also, but using short names does sound a bit easier

Gordon Linoff Over a year ago

@IssaChanzi . . . For the naming of entities in the database, I strongly encourage long, descriptive names. Within a single query, they tend to be clunky -- something like choosing i instead of index or incremeting_number in other languages.

Collectives™ on Stack Overflow

Unknown column error accessing outer query column inside subquery

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related