Oliver Dizon schrieb am 27.01.2015 um 11:46: > I hope I'm in the right place to throw this. I just want to ask the reason behind this weird scenario. > > All records were deleted in a table even if the subquery in the where clause has a missing a column. > > --from a certain script where table_id is not yet existing on table_sample > > delete from main_table_sample where table_id in (select table_id from table_sample); > > Im expecting that postgres will return an ERROR message like > 'column "table_id" does not exist on table table_sample' > > But it did not returned any error and deleted all the records instead. > > Version: psql (8.4.9, server 9.2.5) >No, this is correct and complies with the SQL standard. If you reference a column in a sub-select that is available in the outer query but not in the sub-select, then it's automatically a reference to the column from the outer query. >So in your query, the column "table_id" in the sub-query references the main_table_sample.table_id. >I agree this is confusing, but it's the way it was specified in the SQL standard. >Thomas Yea. I've realized that it's yet another example of "outer reference". I guess I really need to read another SQL book again :-) Thanks! ______________________ Oliver G. Dizon Z Getcare Systems Team RTZ Associates, Inc. johnoliver@xxxxxxxxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
