Solved

Problem with Sub-query

Posted on 2013-10-25
6
456 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

856 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