SQL inner query conundrum
I recently suffered through a minor database disaster. The query I meant to run was something like this
Code:
delete from table_a where x_id in (select x_id from table_b where field_a = 2345) Code:
delete from table_a where x_id in (select x_id from table_c where field_a = 2345) This was run on SQL Server. I verified the behavior substituting 'select count(*)' for delete. I was shocked by this since my expectation is that the query would fail however I found this on a Microsoft page discussing subqueries: Quote:
Code:
delete from table_a where x_id in (select x_id from table_a) To me it makes no sense. |
As no one else has stepped up I'll offer a few comments.
Code:
delete from table_a where x_id in (select x_id from table_b where field_a = 2345) I suppose the relevant logic is that described by MySQL as correlated subqueries, which permits references to tables in the outer query from within the subquery. That combined with implicit qualification of field names results in this behavior. BUT: Quote:
Second comment: My own habit is to explicitly qualify table.column in all but the simplest queries, and to avoid subqueries wherever possible as it is almost always possible|better to do it with a join. For the example you posted my own approach would have been something like... Code:
DELETE table_a FROM table_b JOIN table_a USING(x_id) where table_b.field_a=2345 |
Quote:
Quote:
|
All times are GMT -5. The time now is 01:21 PM. |