Solved

Problem with Sub-query

Posted on 2013-10-25
6
464 Views
Last Modified: 2013-10-25
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?
0
Comment
Question by:navajo26354
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:Barry62
ID: 39601364
I see a CDMTransID.  Is that the field you are looking for?
0
 
LVL 8

Expert Comment

by:Barry62
ID: 39601371
Show us your create statement for TransIDsToDelete
0
 
LVL 3

Expert Comment

by:coreconcepts
ID: 39601378
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:navajo26354
ID: 39601404
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
 
LVL 3

Expert Comment

by:coreconcepts
ID: 39601491
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
 
LVL 3

Accepted Solution

by:
coreconcepts earned 500 total points
ID: 39601549
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

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question