June 28, 2006 at 8:32 am
Hi All,
We have renamed a column in a table which is used in a sub query in a stored proc, which deletes records on specific condition. Is Sql Server 2005/2000 have bug?
I have a query like below
First we have sp with
DELETE Table1 WHERE Table1.Column1 IN (SELECT TABLE2.Column2 FROM TABLE2 WHERE TABLE2.X1=100 AND TABLE2.X2=200)
later we changed TABLE2.Column2 is renamed to TABLE2.Col2.
When we rerun sp it deleted all records from table1 it found X1=100 and X2=200.
Donot not suggest to recompile the Sp. I ran same query in QueryAnalyser (SQL Query EDItor in sql server 2005) And It deleted.
We migrated from SQL server 2000 to SQL SERVER 2005 recently. May be problem lying in SQL SERVER 2000 too.
Do you have any idea why such disasterous behavior.
Regards
Soumya
June 28, 2006 at 10:10 am
No need to recompile your stored procedure. The SP in any version of SQL Server will return an error. Please check your SP again OR send exact code.
June 28, 2006 at 10:19 am
Thanks for reply. Is it true that I can mention any Column name from Table1 in Sub Query and produce the problem. Both tables had same column name and later we renamed in Table 2.
My actual query is some what like this
DELETE Table1 WHERE Table1.Column1 IN (SELECT Column1 FROM TABLE2 WHERE X1=100 AND X2=200)
In which
Col1, X1 and X2 are from Table 2 and Col2 is before Column1 and I never changed the query.
And Column 1 is from Table1.
June 29, 2006 at 9:04 am
If the latest query you posted is accurate (the real query is always more useful--you can inadvertantly fix the problem while "cleaning up" for posting), I think I see the problem and, no, it's not a bug.
Here's the query:
DELETE Table1 WHERE Table1.Column1 IN (SELECT Column1 FROM TABLE2 WHERE X1=100 AND X2=200)
Before the column name change, this is how SQL Server qualified the query:
DELETE Table1 WHERE Table1.Column1 IN (SELECT TABLE2.Column1 FROM TABLE2 WHERE TABLE2.X1=100 AND TABLE2.X2=200)
This is the desired behavior. After you changed the column name in TABLE2, SQL Server found another way to execute the query:
DELETE Table1 WHERE Table1.Column1 IN (SELECT Table1.Column1 FROM TABLE2 WHERE Table2.X1=100 AND Table2.X2=200)
Notice that TABLE2.Column1 has changed to Table1.Column1. Since the column wasn't qualified with the table name, SQL Server has to try to figure out where that column comes from.
Now, it's a correlated subquery, and the IN clause is true if ANY ROW in Table 2 satisfies the subquery's where clause: X1=100 AND X2=200. So, if any row in Table 2 satisfies this, all rows in Table 1 will be deleted.
How to prevent this? The column name that was identical in two tables should certainly have been qualified with the table name every place in the query. In fact, when more than one table is in a query, it's good practice to qualify all column names with their respective table names.
June 29, 2006 at 9:14 am
Thanks Stephanie,
It helped me. Yep I have to know more about how Sql engines interpret queries and requested columns.
regards
Soumya
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply