Problem with Sub-query

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?
navajo26354Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
coreconceptsConnect With a Mentor Commented:
Navajo, again my above comment will work fine, but just to add to your solution.. if you wanted to make an archive table of the entire row(s) you delete from the NoDecisionHistory table... you could do it all in two statements

The first creates a copy of your NoDecisionHistory table called NoDecisionHistory_Archive...

then you insert into it the OUTPUT of your delete statement (same one from my last comment) as follows:


-- this will create an archive table of your exact table
-- but it won't include constraints
SELECT * INTO DBO.CDMNoDecisionHistory_Archive
FROM [dbo].[CDMNoDecisionHistory]
WHERE 1 = 0

-- Now do your delete in the inner query of this
-- insert statement using the output clause
INSERT INTO DBO.CDMNoDecisionHistory_Archive
SELECT *
FROM (
            DELETE FROM H
            OUTPUT deleted.*  
            FROM dbo.CDMNoDecisionHistory H
            INNER JOIN TRANS T
                   ON T.OrgID = H.TransID      
            WHERE T.OrgID IN (
                                            3995,
                                            4155,
                                            4123
                                      )
        ) as D
0
 
Barry62Commented:
I see a CDMTransID.  Is that the field you are looking for?
0
 
Barry62Commented:
Show us your create statement for TransIDsToDelete
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
coreconceptsCommented:
Hi Navajo, can you link the code for the temp tables please.  From what you said, TransID is the right column, and your code should work just fine provided that (a) you use the transid column in your subquery and (b) TransID is the correct column name and the column with corresponding values.   The fact that it is telling you that your column is incorrect is definitely the problem.  My initial guess isbtypo and or you should alias the table and use alias.column name to identify
0
 
navajo26354Author Commented:
Here is what I do the create the temp table:

SELECT * INTO TransIDsToDelete  FROM Trans WHERE OrgID IN
(
3995,
4155,
4123
)

This gives me two columns, ID and OrgID.  I then rename the ID column to TransID.  Perhaps this is causing the problem?
0
 
coreconceptsCommented:
Hmm... no need for a temp table -- just do this:

DELETE FROM H
FROM dbo.CDMNoDecisionHistory H
INNER JOIN TRANS T
     ON T.OrgID = H.TransID      
WHERE T.OrgID IN (
                      3995,
                      4155,
                      4123
                  )

and if you want to see what you're about to delete... just replace the DELETE FROM H with SELECT * FROM H like this

SELECT *
FROM dbo.CDMNoDecisionHistory H
INNER JOIN TRANS T
     ON T.OrgID = H.TransID      
WHERE T.OrgID IN (
                      3995,
                      4155,
                      4123
                  )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.