I have two tables, one is a temp table and the other is a permanent. The temp table is named TempIDsToDelete. It has two columns, TransID and OrgID. The permanent table is named CDMNoDecisionHistory. The code to create it is as follows:
CREATE TABLE [dbo].[CDMNoDecisionHistory](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[CDMTransID] [bigint] NOT NULL,
[OrgID] [int] NULL,
[ArchiveUID] [bigint] NULL,
CONSTRAINT [PK_CDMNoDecisionHistory] PRIMARY KEY CLUSTERED
( [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
I'm attempting to delete records from the permanent table, where CDMNoDecisionHistory.CDMTransID = TransIDsToDelete.TransID using the following query:
DELETE FROM CITI_GTS12..CDMNoDecisionHistory WHERE CDMTransID IN
(SELECT ID FROM TransIDsToDelete)
Now here's my question: I realize there is a mistake in my inner select statement. I used the column name ID instead of TransID. When I attempt to run this query using the incorrect column, it does a volume of work and reports no errors, even though the ID column is incorrect. The records deleted and much greater than they should be.
To make it more confusing, if I replace ID with TransID in my inner query, it tells me I have an incorrect column name. What do I need to do to correct this problem?