Solved

Problem with Sub-query

Posted on 2013-10-25
6
445 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now