T-SQL Query Explanation

I am writing a query to archive some data in a few of our huge tables in our ERP system.
I made a mistake in my delete statement and would like for someone to explain to me exactly how the delete query ran at all. Here is the full query:
/* IC BIN MOVEMENTS */

CREATE TABLE #BINMOVEMENTS(
	[OLD_GEM_DBKEY] [int] NOT NULL, --WILL BE ORIGINAL GEM_DBKEY FROM IC_BIN_MOVEMENTS
	[COMPANY_CODE] [varchar](10) NOT NULL,
	[WAREHOUSE] [varchar](2) NOT NULL,
	[PART_CODE] [varchar](15) NULL,
	[BIN_LOCATION] [varchar](15) NOT NULL,
	[MH_NUMBER] [int] NOT NULL,
	[DOCUMENT_NUMBER] [varchar](13) NOT NULL,
	[DOCUMENT_LINE] [varchar](9) NULL,
	[DOCUMENT_SEQUENCE] [smallint] NOT NULL,
	[UNIT_OF_MEASURE] [varchar](4) NULL,
	[SOURCE_MODULE] [varchar](2) NULL,
	[TRANSACTION_TYPE] [varchar](2) NULL,
	[FACTORY] [varchar](2) NOT NULL,
	[WAREHOUSE_ZONE] [varchar](6) NOT NULL,
	[MOVEMENT_DATE] [datetime] NULL,
	[SYSTEM_DATE] [datetime] NOT NULL,
	[USERNAME] [varchar](12) NOT NULL,
	[SYS_CALLING_FUNCTION] [varchar](15) NOT NULL,
	[IC_STATUS_CODE] [varchar](8) NOT NULL,
	[IC_LOT_NUMBER] [varchar](15) NOT NULL,
	[IC_LOT_SEQUENCE] [int] NOT NULL,
	[IC_QUANTITY_FIELD] [varchar](30) NOT NULL,
	[IC_MOVE_QUANTITY] [numeric](20, 6) NOT NULL,
	[IC_PRODUCT_UOM_TYPE] [varchar](1) NOT NULL,
	[MOVEMENT_CODE] [varchar](6) NULL,
	[TRANSACTION_DESCRIPTION] [varchar](30) NULL,
	[PERIOD] [smallint] NOT NULL,
	[YEAR] [smallint] NOT NULL,
	[GEM_TRANSACTION_ID] [varchar](64) NULL
)

INSERT INTO #BINMOVEMENTS (
	OLD_GEM_DBKEY,
	COMPANY_CODE,
	WAREHOUSE,
	PART_CODE,
	BIN_LOCATION,
	MH_NUMBER,
	DOCUMENT_NUMBER,
	DOCUMENT_LINE,
	DOCUMENT_SEQUENCE,
	UNIT_OF_MEASURE,
	SOURCE_MODULE,
	TRANSACTION_TYPE,
	FACTORY,
	WAREHOUSE_ZONE,
	MOVEMENT_DATE,
	SYSTEM_DATE,
	USERNAME,
	SYS_CALLING_FUNCTION,
	IC_STATUS_CODE,
	IC_LOT_NUMBER,
	IC_LOT_SEQUENCE,
	IC_QUANTITY_FIELD,
	IC_MOVE_QUANTITY,
	IC_PRODUCT_UOM_TYPE,
	MOVEMENT_CODE,
	TRANSACTION_DESCRIPTION,
	PERIOD,
	[YEAR],
	GEM_TRANSACTION_ID
) 
(
SELECT C.[GEM_DBKEY]
	  ,C.[COMPANY_CODE]
      ,C.[WAREHOUSE]
      ,C.[PART_CODE]
      ,C.[BIN_LOCATION]
      ,C.[MH_NUMBER]
      ,C.[DOCUMENT_NUMBER]
      ,C.[DOCUMENT_LINE]
      ,C.[DOCUMENT_SEQUENCE]
      ,C.[UNIT_OF_MEASURE]
      ,C.[SOURCE_MODULE]
      ,C.[TRANSACTION_TYPE]
      ,C.[FACTORY]
      ,C.[WAREHOUSE_ZONE]
      ,C.[MOVEMENT_DATE]
      ,C.[SYSTEM_DATE]
      ,C.[USERNAME]
      ,C.[SYS_CALLING_FUNCTION]
      ,C.[IC_STATUS_CODE]
      ,C.[IC_LOT_NUMBER]
      ,C.[IC_LOT_SEQUENCE]
      ,C.[IC_QUANTITY_FIELD]
      ,C.[IC_MOVE_QUANTITY]
      ,C.[IC_PRODUCT_UOM_TYPE]
      ,C.[MOVEMENT_CODE]
      ,C.[TRANSACTION_DESCRIPTION]
      ,C.[PERIOD]
      ,C.[YEAR]
      ,C.[GEM_TRANSACTION_ID]
  FROM [fin_prod].[dbo].[IC_BIN_MOVEMENTS] C
  INNER JOIN DC_LICENSE_PLATE_LINES L
  ON C.COMPANY_CODE = L.COMPANY_CODE
  AND C.PART_CODE=L.PART_CODE
  AND C.IC_LOT_NUMBER = L.IC_LOT_NUMBER
  RIGHT OUTER JOIN DC_LICENSE_PLATES P
  ON P.COMPANY_CODE = L.COMPANY_CODE
  AND P.DC_LICENSE_PLATE_ID = L.DC_LICENSE_PLATE_ID
  WHERE MOVEMENT_DATE < GETDATE()-365
  AND P.DC_LICENSE_PLATE_STATUS IN('INACTIVE','SHIPPED')
  AND C.GEM_DBKEY IS NOT NULL
)

INSERT INTO [fin_prod].[dbo].[AER_IC_BIN_MOVEMENTS_ARC]
           ([COMPANY_CODE]
           ,[WAREHOUSE]
           ,[PART_CODE]
           ,[BIN_LOCATION]
           ,[MH_NUMBER]
           ,[DOCUMENT_NUMBER]
           ,[DOCUMENT_LINE]
           ,[DOCUMENT_SEQUENCE]
           ,[UNIT_OF_MEASURE]
           ,[SOURCE_MODULE]
           ,[TRANSACTION_TYPE]
           ,[FACTORY]
           ,[WAREHOUSE_ZONE]
           ,[MOVEMENT_DATE]
           ,[SYSTEM_DATE]
           ,[USERNAME]
           ,[SYS_CALLING_FUNCTION]
           ,[IC_STATUS_CODE]
           ,[IC_LOT_NUMBER]
           ,[IC_LOT_SEQUENCE]
           ,[IC_QUANTITY_FIELD]
           ,[IC_MOVE_QUANTITY]
           ,[IC_PRODUCT_UOM_TYPE]
           ,[MOVEMENT_CODE]
           ,[TRANSACTION_DESCRIPTION]
           ,[PERIOD]
           ,[YEAR]
           ,[GEM_TRANSACTION_ID]
           ,[OLD_GEM_DBKEY])

(SELECT [COMPANY_CODE]
           ,[WAREHOUSE]
           ,[PART_CODE]
           ,[BIN_LOCATION]
           ,[MH_NUMBER]
           ,[DOCUMENT_NUMBER]
           ,[DOCUMENT_LINE]
           ,[DOCUMENT_SEQUENCE]
           ,[UNIT_OF_MEASURE]
           ,[SOURCE_MODULE]
           ,[TRANSACTION_TYPE]
           ,[FACTORY]
           ,[WAREHOUSE_ZONE]
           ,[MOVEMENT_DATE]
           ,[SYSTEM_DATE]
           ,[USERNAME]
           ,[SYS_CALLING_FUNCTION]
           ,[IC_STATUS_CODE]
           ,[IC_LOT_NUMBER]
           ,[IC_LOT_SEQUENCE]
           ,[IC_QUANTITY_FIELD]
           ,[IC_MOVE_QUANTITY]
           ,[IC_PRODUCT_UOM_TYPE]
           ,[MOVEMENT_CODE]
           ,[TRANSACTION_DESCRIPTION]
           ,[PERIOD]
           ,[YEAR]
           ,[GEM_TRANSACTION_ID]
           ,[OLD_GEM_DBKEY]
FROM #BINMOVEMENTS)

DELETE FROM dbo.IC_BIN_MOVEMENTS
WHERE GEM_DBKEY IN(SELECT GEM_DBKEY FROM #BINMOVEMENTS)

DROP TABLE #BINMOVEMENTS

Open in new window


In the delete statement's subquery, I had the wrong field selected and the field I put in there doesn't exist. In (SELECT GEM_DBKEY FROM #BINMOVEMENTS) the field GEM_DBKEY should have been OLD_GEM_DBKEY. When I run that subquery select statement, I get the following error.

 /*------------------------
SELECT GEM_DBKEY FROM #BINMOVEMENTS
------------------------*/
Msg 207, Level 16, State 1, Line 1
Invalid column name 'GEM_DBKEY'.

I would expect to get this error if the field isn't found. However, when I ran the delete statement above, it deleted EVERY RECORD in the IC_BIN_MOVEMENTS table. I am hoping someone could explain that to me because it is escaping me now. We are using APEX SQL to try to get the data restored. But it will take a while to restore 22m rows instead of the 5.5m that *should* have been deleted. I'm just trying to understand WHY it deleted anything.
Carla RomereDirector of Information TechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Wild guess>
Any change that only the DELETE FROM dbo.IC_BIN_MOVEMENTS line was highlighted when it was executed?
If a selection is made and the Execute button is hit, it executes only the selection.
0
Carla RomereDirector of Information TechnologyAuthor Commented:
No, I made absolutely sure that both rows were highlighted. I just did not catch that I hadn't updated the field name in the subquery.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Try that on a new query window, sql server wont drop the actual table definition even if you delete them manually.
check my blog here
http://sequelserver.blogspot.ca/2013/04/did-drop-table-temp-actually-deletes.html
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Carla RomereDirector of Information TechnologyAuthor Commented:
I haven't run the drop (temp) table statement. I ONLY ran the delete statement by itself (after running everything above the delete statement - one statement at a time). The temp table is still there because I haven't closed that session yet. I'm just curious why EVERY row was deleted in the IC_BIN_MOVEMENTS table when the select query for the delete referenced a field that didn't exist.
0
Aneesh RetnakaranDatabase AdministratorCommented:
if you are using APEX , then why don't you find the statement that ran the delete
0
Carla RomereDirector of Information TechnologyAuthor Commented:
We are still pulling the logs. I just thought there might be an error in my syntax or something and wondered if anyone could tell me.
0
Aneesh RetnakaranDatabase AdministratorCommented:
I cannot think of any cases how your query can run ?
0
Scott PletcherSenior DBACommented:
>>
DELETE FROM dbo.IC_BIN_MOVEMENTS
WHERE GEM_DBKEY IN(SELECT GEM_DBKEY FROM #BINMOVEMENTS)
<<

The statement will indeed run just fine.

If column "GEM_DBKEY" does not exist in #BINMOVEMENTS, then you will be referring to the column in the outer table(s), in this case IC_BIN_MOVEMENTS.

By default in a subquery, a column refers to the most local table, in this case #BINMOVEMENTS. However, if that table doesn't contain such a column, SQL will use the outer table's column by that name.

This allows you the convenience of leaving off the table name/alias ... albeit potentially dangerous.

For example, say cola is only in table1 and colb is only in table2; the code below works fine because SQL "knows" to use the cola from table1:
SELECT *
FROM dbo.table1
WHERE EXISTS(SELECT 1 FROM dbo.table2 WHERE colb = cola)

If cola existed in both tables, you could do this:
SELECT *
FROM dbo.table1 t1
WHERE EXISTS(SELECT 1 FROM dbo.table2 t2 WHERE cola = t1.cola)
The unqualified "cola" will use table2; to compare to table1, you must explicitly qualify that column, since it exists in both tables.


My rule to help avoid this and other multi-table issues is to always, and I mean ALWAYS, qualify every column with the appropriate table alias anytime two or more tables are involved.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Carla RomereDirector of Information TechnologyAuthor Commented:
Okay that makes perfect sense and was exactly what I wanted to find out. Thank you so much for explaining that to me!
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Nice catch Scott.  Missed that one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.